<!DOCTYPE html>
<!-- saved from url=(0060)https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html -->
<html lang="zh-cn"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<meta name="viewport" content="width=device-width, initial-scale=1">
<title>mysql sql语句大全 - yfProgramer - 博客园</title>
<link type="text/css" rel="stylesheet" href="./mysql sql语句大全 - yfProgramer - 博客园_files/blog-common.css">
<link id="MainCss" type="text/css" rel="stylesheet" href="./mysql sql语句大全 - yfProgramer - 博客园_files/bundle-Valentine.css">
<link id="mobile-style" media="only screen and (max-width: 767px)" type="text/css" rel="stylesheet" href="./mysql sql语句大全 - yfProgramer - 博客园_files/bundle-Valentine-mobile.css">
<link title="RSS" type="application/rss+xml" rel="alternate" href="http://www.cnblogs.com/yunf/rss">
<link title="RSD" type="application/rsd+xml" rel="EditURI" href="http://www.cnblogs.com/yunf/rsd.xml">
<link type="application/wlwmanifest+xml" rel="wlwmanifest" href="http://www.cnblogs.com/yunf/wlwmanifest.xml">
<script async="" src="./mysql sql语句大全 - yfProgramer - 博客园_files/analytics.js.下载"></script><script src="./mysql sql语句大全 - yfProgramer - 博客园_files/jquery.js.下载" type="text/javascript"></script><style type="text/css" abt="234"></style>  
<script type="text/javascript">var currentBlogApp = 'yunf', cb_enable_mathjax=false;var isLogined=true;</script>
<script src="./mysql sql语句大全 - yfProgramer - 博客园_files/blog-common.js.下载" type="text/javascript"></script>
<script>//console.log('a')
</script><script>doAdblock();
function doAdblock(){
    (function() {
        function A() {}
        A.prototype = {
            rules: {
                'pps_pps': {
                    'find': /^http:\/\/www\.iqiyi\.com\/player\/cupid\/common\/pps_flvplay_s\.swf/,
                    'replace': 'http://swf.adtchrome.com/pps_20140420.swf'
                },
                '17173_in':{
                    'find':/http:\/\/f\.v\.17173cdn\.com\/(\d+\/)?flash\/PreloaderFile(Customer)?\.swf/,
                    'replace':"http://swf.adtchrome.com/17173_in_20150522.swf"
                },
                '17173_out':{
                    'find':/http:\/\/f\.v\.17173cdn\.com\/(\d+\/)?flash\/PreloaderFileFirstpage\.swf/,
                    'replace':"http://swf.adtchrome.com/17173_out_20150522.swf"
                },
                '17173_live':{
                    'find':/http:\/\/f\.v\.17173cdn\.com\/(\d+\/)?flash\/Player_stream(_firstpage)?\.swf/,
                    'replace':"http://swf.adtchrome.com/17173_stream_20150522.swf"
                },
                '17173_live_out':{
                    'find':/http:\/\/f\.v\.17173cdn\.com\/(\d+\/)?flash\/Player_stream_(custom)?Out\.swf/,
                    'replace':"http://swf.adtchrome.com/17173.out.Live.swf"
                }
            },
            _done: null,
            get done() {
                if(!this._done) {
                    this._done = new Array();
                }
                return this._done;
            },
            addAnimations: function() {
                var style = document.createElement('style');
                style.type = 'text/css';
                style.innerHTML = 'object,embed{\
                -webkit-animation-duration:.001s;-webkit-animation-name:playerInserted;\
                -ms-animation-duration:.001s;-ms-animation-name:playerInserted;\
                -o-animation-duration:.001s;-o-animation-name:playerInserted;\
                animation-duration:.001s;animation-name:playerInserted;}\
                @-webkit-keyframes playerInserted{from{opacity:0.99;}to{opacity:1;}}\
                @-ms-keyframes playerInserted{from{opacity:0.99;}to{opacity:1;}}\
                @-o-keyframes playerInserted{from{opacity:0.99;}to{opacity:1;}}\
                @keyframes playerInserted{from{opacity:0.99;}to{opacity:1;}}';
                document.getElementsByTagName('head')[0].appendChild(style);
            },
            animationsHandler: function(e) {
                if(e.animationName === 'playerInserted') {
                    this.replace(e.target);
                }
            },
            replace: function(elem) {
                if (/http:\/\/v.youku.com\/v_show\/.*/.test(window.location.href)){
                    var tag = document.getElementById("playerBox").getAttribute("player")
                    if (tag == "adt"){
                        console.log("adt adv")
                        return;
                    }
                }
                if(this.done.indexOf(elem) != -1) return;
                this.done.push(elem);
                var player = elem.data || elem.src;
                if(!player) return;
                var i, find, replace = false;
                for(i in this.rules) {
                    find = this.rules[i]['find'];
                    if(find.test(player)) {
                        replace = this.rules[i]['replace'];
                        if('function' === typeof this.rules[i]['preHandle']) {
                            this.rules[i]['preHandle'].bind(this, elem, find, replace, player)();
                        }else{
                            this.reallyReplace.bind(this, elem, find, replace)();
                        }
                        break;
                    }
                }
            },
            reallyReplace: function(elem, find, replace) {
                elem.data && (elem.data = elem.data.replace(find, replace)) || elem.src && ((elem.src = elem.src.replace(find, replace)) && (elem.style.display = 'block'));
                var b = elem.querySelector("param[name='movie']");
                this.reloadPlugin(elem);
            },
            reloadPlugin: function(elem) {
                var nextSibling = elem.nextSibling;
                var parentNode = elem.parentNode;
                parentNode.removeChild(elem);
                var newElem = elem.cloneNode(true);
                this.done.push(newElem);
                if(nextSibling) {
                    parentNode.insertBefore(newElem, nextSibling);
                } else {
                    parentNode.appendChild(newElem);
                }
            },
            init: function() {
                var handler = this.animationsHandler.bind(this);
                document.body.addEventListener('webkitAnimationStart', handler, false);
                document.body.addEventListener('msAnimationStart', handler, false);
                document.body.addEventListener('oAnimationStart', handler, false);
                document.body.addEventListener('animationstart', handler, false);
                this.addAnimations();
            }
        };
        new A().init();
    })();
}
// 20140730
(function cnbeta() {
    if (document.URL.indexOf('cnbeta.com') >= 0) {
        var elms = document.body.querySelectorAll("p>embed");
        Array.prototype.forEach.call(elms, function(elm) {
            elm.style.marginLeft = "0px";
        });
    }
})();
//baidu
if(document.URL.indexOf('www.baidu.com') >= 0){
    if(document && document.getElementsByTagName && document.getElementById && document.body){
        var aa = function(){
            var all = document.body.querySelectorAll("#content_left div,#content_left table");
            for(var i = 0; i < all.length; i++){
                if(/display:\s?(table|block)\s!important/.test(all[i].getAttribute("style"))){all[i].style.display= "none";all[i].style.visibility='hidden';}
            }
            all = document.body.querySelectorAll('.result.c-container[id="1"]');
            //if(all.length == 1) return;
            for(var i = 0; i < all.length; i++){
                if(all[i].innerHTML && all[i].innerHTML.indexOf('广告')>-1){
                    all[i].style.display= "none";all[i].style.visibility='hidden';
                }
            }
        }
        aa();
        document.getElementById('wrapper_wrapper').addEventListener('DOMSubtreeModified',aa)
    };
}
// 20140922
(function kill_360() {
    if (document.URL.indexOf('so.com') >= 0) {
        document.getElementById("e_idea_pp").style.display = none;
    }
})();
if (document.URL.indexOf("tv.sohu.com") >= 0){
    if (document.cookie.indexOf("fee_status=true")==-1){document.cookie='fee_status=true'};
}
if (document.URL.indexOf("56.com") >= 0){
    if (document.cookie.indexOf("fee_status=true")==-1){document.cookie='fee_status=true'};
}
if (document.URL.indexOf("iqiyi.com") >= 0){
    if (document.cookie.indexOf("player_forcedType=h5_VOD")==-1){
        document.cookie='player_forcedType=h5_VOD'
        if(localStorage.reloadTime && Date.now() - parseInt(localStorage.reloadTime)<60000){
            console.log('no reload')
        }else{
            location.reload()
            localStorage.reloadTime = Date.now();
        }
    }
}
</script><style type="text/css">object,embed{                -webkit-animation-duration:.001s;-webkit-animation-name:playerInserted;                -ms-animation-duration:.001s;-ms-animation-name:playerInserted;                -o-animation-duration:.001s;-o-animation-name:playerInserted;                animation-duration:.001s;animation-name:playerInserted;}                @-webkit-keyframes playerInserted{from{opacity:0.99;}to{opacity:1;}}                @-ms-keyframes playerInserted{from{opacity:0.99;}to{opacity:1;}}                @-o-keyframes playerInserted{from{opacity:0.99;}to{opacity:1;}}                @keyframes playerInserted{from{opacity:0.99;}to{opacity:1;}}</style></head>
<body>
<a name="top"></a>

<!--done-->
<table cellpadding="0" cellspacing="0" border="0" align="center" width="100%">
<tbody><tr>
	<td width="184" background="./mysql sql语句大全 - yfProgramer - 博客园_files/banner1.gif"></td>
	<td background="./mysql sql语句大全 - yfProgramer - 博客园_files/banner.gif">
<!--done-->
<div class="header">
	<div class="headerText">
		<a id="Header1_HeaderTitle" class="headermaintitle" href="http://www.cnblogs.com/yunf/">yfProgramer</a><br>
		<span style="font-size:12px;color:#4371A6;padding-left:20;">学习不是应付生活，应付答案。是一种追求，一个理想。</span>
	</div>
</div>
</td>
	<td width="295" background="./mysql sql语句大全 - yfProgramer - 博客园_files/banner2.gif"></td>
</tr>
</tbody></table>
<div id="mylinks">
<!--done-->
<a id="blog_nav_sitehome" class="menu" href="http://www.cnblogs.com/">博客园</a>&nbsp;&nbsp;&nbsp;<a id="blog_nav_myhome" class="mainmenu" href="http://www.cnblogs.com/yunf/">首页</a>&nbsp;&nbsp;&nbsp;<a id="blog_nav_newpost" class="mainmenu" rel="nofollow" href="https://i.cnblogs.com/EditPosts.aspx?opt=1">新随笔</a>&nbsp;&nbsp;&nbsp;<a id="MyLinks1_NewArticleLink" class="mainmenu" href="https://www.cnblogs.com/EnterMyBlog.aspx?NewArticle=1">新文章</a>&nbsp;&nbsp;&nbsp;<a id="blog_nav_contact" accesskey="9" class="mainmenu" rel="nofollow" href="https://msg.cnblogs.com/send/yfProgramer">联系</a>&nbsp;&nbsp;&nbsp;<a id="blog_nav_rss" class="mainmenu" href="http://www.cnblogs.com/yunf/rss">订阅</a><a id="blog_nav_rss_image" href="http://www.cnblogs.com/yunf/rss"><img src="./mysql sql语句大全 - yfProgramer - 博客园_files/xml.gif" alt="订阅"></a>&nbsp;&nbsp;&nbsp;<a id="blog_nav_admin" class="menu" rel="nofollow" href="https://i.cnblogs.com/">管理</a>
</div>
<div id="mytopmenu">
	
		<div id="mystats">
			<div id="blog_stats">
<!--done-->
<div class="blogStats">
posts - 27,comments - 36,trackbacks - 0

</div></div></div>
	
</div>
<div id="leftcontent">
	
		<div id="leftcontentcontainer">
			<div id="blog-calendar" style=""><table id="blogCalendar" class="Cal" cellspacing="0" cellpadding="0" title="Calendar">
	<tbody><tr><td colspan="7"><table class="CalTitle" cellspacing="0">
		<tbody><tr><td class="CalNextPrev"><a href="javascript:void(0);" onclick="loadBlogCalendar(&#39;2017/10/01&#39;);return false;">&lt;</a></td><td align="center">2017年11月</td><td class="CalNextPrev" align="right"><a href="javascript:void(0);" onclick="loadBlogCalendar(&#39;2017/12/01&#39;);return false;">&gt;</a></td></tr>
	</tbody></table></td></tr><tr><th class="CalDayHeader" align="center" abbr="日" scope="col">日</th><th class="CalDayHeader" align="center" abbr="一" scope="col">一</th><th class="CalDayHeader" align="center" abbr="二" scope="col">二</th><th class="CalDayHeader" align="center" abbr="三" scope="col">三</th><th class="CalDayHeader" align="center" abbr="四" scope="col">四</th><th class="CalDayHeader" align="center" abbr="五" scope="col">五</th><th class="CalDayHeader" align="center" abbr="六" scope="col">六</th></tr><tr><td class="CalOtherMonthDay" align="center">29</td><td class="CalOtherMonthDay" align="center">30</td><td class="CalOtherMonthDay" align="center">31</td><td align="center">1</td><td align="center">2</td><td align="center">3</td><td class="CalWeekendDay" align="center">4</td></tr><tr><td class="CalWeekendDay" align="center">5</td><td align="center">6</td><td align="center">7</td><td align="center">8</td><td align="center">9</td><td align="center">10</td><td class="CalWeekendDay" align="center">11</td></tr><tr><td class="CalWeekendDay" align="center">12</td><td align="center">13</td><td align="center">14</td><td align="center">15</td><td align="center">16</td><td align="center">17</td><td class="CalWeekendDay" align="center">18</td></tr><tr><td class="CalTodayDay" align="center">19</td><td align="center">20</td><td align="center">21</td><td align="center">22</td><td align="center">23</td><td align="center">24</td><td class="CalWeekendDay" align="center">25</td></tr><tr><td class="CalWeekendDay" align="center">26</td><td align="center">27</td><td align="center">28</td><td align="center">29</td><td align="center">30</td><td class="CalOtherMonthDay" align="center">1</td><td class="CalOtherMonthDay" align="center">2</td></tr><tr><td class="CalOtherMonthDay" align="center">3</td><td class="CalOtherMonthDay" align="center">4</td><td class="CalOtherMonthDay" align="center">5</td><td class="CalOtherMonthDay" align="center">6</td><td class="CalOtherMonthDay" align="center">7</td><td class="CalOtherMonthDay" align="center">8</td><td class="CalOtherMonthDay" align="center">9</td></tr>
</tbody></table></div><script type="text/javascript">loadBlogDefaultCalendar();</script><br>
			
<!--done-->
<div class="newsItem">
	<div id="blog-news"><div id="profile_block">昵称：<a href="http://home.cnblogs.com/u/yunf/">yfProgramer</a><br>园龄：<a href="http://home.cnblogs.com/u/yunf/" title="入园时间：2011-03-22">6年7个月</a><br>粉丝：<a href="http://home.cnblogs.com/u/yunf/followers/">127</a><br>关注：<a href="http://home.cnblogs.com/u/yunf/followees/">0</a><div id="p_b_follow"><a href="javascript:void(0);" onclick="follow(&#39;aa34e74c-2654-e011-a53f-842b2b196315&#39;)">+加关注</a></div></div></div><script type="text/javascript">loadBlogNews();</script>
</div>

			<div id="blog-sidecolumn"><div id="sidebar_search" class="sidebar-block">
<div id="sidebar_search" class="mySearch">
<h3 class="catListTitle">搜索</h3>
<div id="sidebar_search_box">
<div id="widget_my_zzk" class="div_my_zzk"><input type="text" id="q" onkeydown="return zzk_go_enter(event);" class="input_my_zzk">&nbsp;<input onclick="zzk_go()" type="button" value="找找看" id="btnZzk" class="btn_my_zzk"></div>
<div id="widget_my_google" class="div_my_zzk"><input type="text" name="google_q" id="google_q" onkeydown="return google_go_enter(event)" class="input_my_zzk">&nbsp;<input onclick="google_go()" type="button" value="谷歌搜索" class="btn_my_zzk"></div>
</div>
</div>

</div><div id="sidebar_shortcut" class="sidebar-block">
<h3 class="catListTitle">常用链接</h3>
<ul>
<li><a href="http://www.cnblogs.com/yunf/p/" title="我的博客的随笔列表">我的随笔</a></li><li><a href="http://www.cnblogs.com/yunf/MyComments.html" title="我发表过的评论列表">我的评论</a></li><li><a href="http://www.cnblogs.com/yunf/OtherPosts.html" title="我评论过的随笔列表">我的参与</a></li><li><a href="http://www.cnblogs.com/yunf/RecentComments.html" title="我的博客的评论列表">最新评论</a></li><li><a href="http://www.cnblogs.com/yunf/tag/" title="我的博客的标签列表">我的标签</a></li>
</ul>
<div id="itemListLin_con" style="display:none;">

</div></div><div id="sidebar_toptags" class="sidebar-block"></div><div id="sidebar_categories">
<!--done-->

		<h1 class="catListTitle">随笔分类</h1>
		
				<ul class="catList">
			
				<li class="catListItem"> <a id="CatList_LinkList_0_Link_0" class="listitem" href="http://www.cnblogs.com/yunf/category/289545.html">CSS(2)</a></li>
			
				<li class="catListItem"> <a id="CatList_LinkList_0_Link_1" class="listitem" href="http://www.cnblogs.com/yunf/category/289641.html">Javascript(2)</a></li>
			
				<li class="catListItem"> <a id="CatList_LinkList_0_Link_2" class="listitem" href="http://www.cnblogs.com/yunf/category/289218.html">Linux(1)</a></li>
			
				<li class="catListItem"> <a id="CatList_LinkList_0_Link_3" class="listitem" href="http://www.cnblogs.com/yunf/category/291916.html">MySql(4)</a></li>
			
				<li class="catListItem"> <a id="CatList_LinkList_0_Link_4" class="listitem" href="http://www.cnblogs.com/yunf/category/289184.html">PHP(16)</a></li>
			
				</ul>
			
	
		<h1 class="catListTitle">随笔档案</h1>
		
				<ul class="catList">
			
				<li class="catListItem"> <a id="CatList_LinkList_1_Link_0" class="listitem" href="http://www.cnblogs.com/yunf/archive/2011/04.html">2011年4月 (9)</a></li>
			
				<li class="catListItem"> <a id="CatList_LinkList_1_Link_1" class="listitem" href="http://www.cnblogs.com/yunf/archive/2011/03.html">2011年3月 (18)</a></li>
			
				</ul>
			
	
</div><div id="sidebar_recentcomments" class="sidebar-block"><div id="recent_comments_wrap">
<h3 class="catListTitle">最新评论</h3>
<div class="RecentComment" id="RecentComments">
	<div id="RecentCommentsBlock"><ul>
        <li class="recent_comment_title"><a href="http://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3754025">1. Re:mysql sql语句大全</a></li>
        <li class="recent_comment_body">这是sql server的语句,不是mysql</li>
        <li class="recent_comment_author">--b0b0</li>
        <li class="recent_comment_title"><a href="http://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3698949">2. Re:mysql sql语句大全</a></li>
        <li class="recent_comment_body">卧槽 ,这是 sql server 的</li>
        <li class="recent_comment_author">--anuodog</li>
        <li class="recent_comment_title"><a href="http://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3653112">3. Re:mysql sql语句大全</a></li>
        <li class="recent_comment_body">请注明转载 谢谢</li>
        <li class="recent_comment_author">--竹染森光</li>
        <li class="recent_comment_title"><a href="http://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3642534">4. Re:mysql sql语句大全</a></li>
        <li class="recent_comment_body">@liusc看的真仔细 赞...</li>
        <li class="recent_comment_author">--亦兔子敏</li>
        <li class="recent_comment_title"><a href="http://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3639085">5. Re:mysql sql语句大全</a></li>
        <li class="recent_comment_body">首先在这里感谢下楼主，其次我想对那些素质较低的人说别人辛辛苦苦写出的内容你不爱看可以圆润的走，别在这里强调你的存在感。</li>
        <li class="recent_comment_author">--耍耍小性子</li>
</ul>
</div>
</div>
</div></div><div id="sidebar_topviewedposts" class="sidebar-block"><div id="topview_posts_wrap">
<h3 class="catListTitle">阅读排行榜</h3>
<div class="RecentComment" id="TopViewPosts"> 
	<div id="TopViewPostsBlock"><ul><li><a href="http://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html">1. mysql sql语句大全(399445)</a></li><li><a href="http://www.cnblogs.com/yunf/archive/2011/04/20/2022193.html">2. mysql创建表(133982)</a></li><li><a href="http://www.cnblogs.com/yunf/archive/2011/04/20/2021830.html">3. 回车符和换行符区别(44575)</a></li><li><a href="http://www.cnblogs.com/yunf/archive/2011/03/31/2001021.html">4. PHP中的可变参数函数和可选参数函数(18081)</a></li><li><a href="http://www.cnblogs.com/yunf/archive/2011/03/31/2000559.html">5. php中实现继承(6224)</a></li></ul></div>
</div>
</div></div><div id="sidebar_topcommentedposts" class="sidebar-block"><div id="topfeedback_posts_wrap">
<h3 class="catListTitle">评论排行榜</h3>
<div class="RecentComment" id="TopCommentsPosts">
	<div id="TopFeedbackPostsBlock"><ul><li><a href="http://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html">1. mysql sql语句大全(31)</a></li><li><a href="http://www.cnblogs.com/yunf/archive/2011/04/20/2021830.html">2. 回车符和换行符区别(4)</a></li><li><a href="http://www.cnblogs.com/yunf/archive/2011/03/31/2001021.html">3. PHP中的可变参数函数和可选参数函数(1)</a></li></ul></div>
</div></div></div><div id="sidebar_topdiggedposts" class="sidebar-block"><div id="topdigg_posts_wrap">
<h3 class="catListTitle">推荐排行榜</h3>
<div class="RecentComment">
	<div id="TopDiggPostsBlock"><ul><li><a href="http://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html">1. mysql sql语句大全(63)</a></li><li><a href="http://www.cnblogs.com/yunf/archive/2011/04/20/2021830.html">2. 回车符和换行符区别(8)</a></li><li><a href="http://www.cnblogs.com/yunf/archive/2011/04/20/2022193.html">3. mysql创建表(1)</a></li><li><a href="http://www.cnblogs.com/yunf/archive/2011/04/04/2005302.html">4. MySql创建表(1)</a></li><li><a href="http://www.cnblogs.com/yunf/archive/2011/03/31/2001021.html">5. PHP中的可变参数函数和可选参数函数(1)</a></li></ul></div>
</div></div></div></div><script type="text/javascript">loadBlogSideColumn();</script></div>
	
</div>
<div id="centercontent">
	
<div id="post_detail">
<!--done-->
<div class="post">
	<div class="postTitle">
		<a id="cb_post_title_url" class="postTitle2" href="http://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html">mysql sql语句大全</a>
	</div>
	<div id="cnblogs_post_body"><div class="cnblogs_Highlighter">
<div><div id="highlighter_543109" class="syntaxhighlighter nogutter  sql"><div class="toolbar"><span><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#" class="toolbar_item command_help help">?</a></span></div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td class="code"><div class="container"><div class="line number1 index0 alt2"><code class="sql plain">1、说明：创建数据库</code></div><div class="line number2 index1 alt1"><code class="sql keyword">CREATE</code> <code class="sql keyword">DATABASE</code> <code class="sql keyword">database</code><code class="sql plain">-</code><code class="sql keyword">name</code></div><div class="line number3 index2 alt2"><code class="sql plain">2、说明：删除数据库</code></div><div class="line number4 index3 alt1"><code class="sql keyword">drop</code> <code class="sql keyword">database</code> <code class="sql plain">dbname</code></div><div class="line number5 index4 alt2"><code class="sql plain">3、说明：备份sql server</code></div><div class="line number6 index5 alt1"><code class="sql comments">--- 创建 备份数据的 device</code></div><div class="line number7 index6 alt2"><code class="sql plain">USE master</code></div><div class="line number8 index7 alt1"><code class="sql keyword">EXEC</code> <code class="sql plain">sp_addumpdevice </code><code class="sql string">'disk'</code><code class="sql plain">, </code><code class="sql string">'testBack'</code><code class="sql plain">, </code><code class="sql string">'c:\mssql7backup\MyNwind_1.dat'</code></div><div class="line number9 index8 alt2"><code class="sql comments">--- 开始 备份</code></div><div class="line number10 index9 alt1"><code class="sql plain">BACKUP </code><code class="sql keyword">DATABASE</code> <code class="sql plain">pubs </code><code class="sql keyword">TO</code> <code class="sql plain">testBack</code></div><div class="line number11 index10 alt2"><code class="sql plain">4、说明：创建新表</code></div><div class="line number12 index11 alt1"><code class="sql keyword">create</code> <code class="sql keyword">table</code> <code class="sql plain">tabname(col1 type1 [</code><code class="sql color1">not</code> <code class="sql color1">null</code><code class="sql plain">] [</code><code class="sql keyword">primary</code> <code class="sql keyword">key</code><code class="sql plain">],col2 type2 [</code><code class="sql color1">not</code> <code class="sql color1">null</code><code class="sql plain">],..)</code></div><div class="line number13 index12 alt2"><code class="sql plain">根据已有的表创建新表：</code></div><div class="line number14 index13 alt1"><code class="sql plain">A：</code><code class="sql keyword">create</code> <code class="sql keyword">table</code> <code class="sql plain">tab_new </code><code class="sql color1">like</code> <code class="sql plain">tab_old (使用旧表创建新表)</code></div><div class="line number15 index14 alt2"><code class="sql plain">B：</code><code class="sql keyword">create</code> <code class="sql keyword">table</code> <code class="sql plain">tab_new </code><code class="sql keyword">as</code> <code class="sql keyword">select</code> <code class="sql plain">col1,col2… </code><code class="sql keyword">from</code> <code class="sql plain">tab_old definition </code><code class="sql keyword">only</code></div><div class="line number16 index15 alt1"><code class="sql plain">5、说明：删除新表</code></div><div class="line number17 index16 alt2"><code class="sql keyword">drop</code> <code class="sql keyword">table</code> <code class="sql plain">tabname</code></div><div class="line number18 index17 alt1"><code class="sql plain">6、说明：增加一个列</code></div><div class="line number19 index18 alt2"><code class="sql keyword">Alter</code> <code class="sql keyword">table</code> <code class="sql plain">tabname </code><code class="sql keyword">add</code> <code class="sql keyword">column</code> <code class="sql plain">col type</code></div><div class="line number20 index19 alt1"><code class="sql plain">注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加</code><code class="sql keyword">varchar</code><code class="sql plain">类型的长度。</code></div><div class="line number21 index20 alt2"><code class="sql plain">7、说明：添加主键： </code><code class="sql keyword">Alter</code> <code class="sql keyword">table</code> <code class="sql plain">tabname </code><code class="sql keyword">add</code> <code class="sql keyword">primary</code> <code class="sql keyword">key</code><code class="sql plain">(col)</code></div><div class="line number22 index21 alt1"><code class="sql plain">说明：删除主键： </code><code class="sql keyword">Alter</code> <code class="sql keyword">table</code> <code class="sql plain">tabname </code><code class="sql keyword">drop</code> <code class="sql keyword">primary</code> <code class="sql keyword">key</code><code class="sql plain">(col)</code></div><div class="line number23 index22 alt2"><code class="sql plain">8、说明：创建索引：</code><code class="sql keyword">create</code> <code class="sql plain">[</code><code class="sql keyword">unique</code><code class="sql plain">] </code><code class="sql keyword">index</code> <code class="sql plain">idxname </code><code class="sql keyword">on</code> <code class="sql plain">tabname(col….)</code></div><div class="line number24 index23 alt1"><code class="sql plain">删除索引：</code><code class="sql keyword">drop</code> <code class="sql keyword">index</code> <code class="sql plain">idxname</code></div><div class="line number25 index24 alt2"><code class="sql plain">注：索引是不可更改的，想更改必须删除重新建。</code></div><div class="line number26 index25 alt1"><code class="sql plain">9、说明：创建视图：</code><code class="sql keyword">create</code> <code class="sql keyword">view</code> <code class="sql plain">viewname </code><code class="sql keyword">as</code> <code class="sql keyword">select</code> <code class="sql plain">statement</code></div><div class="line number27 index26 alt2"><code class="sql plain">删除视图：</code><code class="sql keyword">drop</code> <code class="sql keyword">view</code> <code class="sql plain">viewname</code></div><div class="line number28 index27 alt1"><code class="sql plain">10、说明：几个简单的基本的sql语句</code></div><div class="line number29 index28 alt2"><code class="sql plain">选择：</code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">table1 </code><code class="sql keyword">where</code> <code class="sql plain">范围</code></div><div class="line number30 index29 alt1"><code class="sql plain">插入：</code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">table1(field1,field2) </code><code class="sql keyword">values</code><code class="sql plain">(value1,value2)</code></div><div class="line number31 index30 alt2"><code class="sql plain">删除：</code><code class="sql keyword">delete</code> <code class="sql keyword">from</code> <code class="sql plain">table1 </code><code class="sql keyword">where</code> <code class="sql plain">范围</code></div><div class="line number32 index31 alt1"><code class="sql plain">更新：</code><code class="sql keyword">update</code> <code class="sql plain">table1 </code><code class="sql keyword">set</code> <code class="sql plain">field1=value1 </code><code class="sql keyword">where</code> <code class="sql plain">范围</code></div><div class="line number33 index32 alt2"><code class="sql plain">查找：</code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">table1 </code><code class="sql keyword">where</code> <code class="sql plain">field1 </code><code class="sql color1">like</code> <code class="sql plain">’%value1%’ </code><code class="sql comments">---like的语法很精妙，查资料!</code></div><div class="line number34 index33 alt1"><code class="sql plain">排序：</code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">table1 </code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">field1,field2 [</code><code class="sql keyword">desc</code><code class="sql plain">]</code></div><div class="line number35 index34 alt2"><code class="sql plain">总数：</code><code class="sql keyword">select</code> <code class="sql color2">count</code> <code class="sql keyword">as</code> <code class="sql plain">totalcount </code><code class="sql keyword">from</code> <code class="sql plain">table1</code></div><div class="line number36 index35 alt1"><code class="sql plain">求和：</code><code class="sql keyword">select</code> <code class="sql color2">sum</code><code class="sql plain">(field1) </code><code class="sql keyword">as</code> <code class="sql plain">sumvalue </code><code class="sql keyword">from</code> <code class="sql plain">table1</code></div><div class="line number37 index36 alt2"><code class="sql plain">平均：</code><code class="sql keyword">select</code> <code class="sql color2">avg</code><code class="sql plain">(field1) </code><code class="sql keyword">as</code> <code class="sql plain">avgvalue </code><code class="sql keyword">from</code> <code class="sql plain">table1</code></div><div class="line number38 index37 alt1"><code class="sql plain">最大：</code><code class="sql keyword">select</code> <code class="sql keyword">max</code><code class="sql plain">(field1) </code><code class="sql keyword">as</code> <code class="sql plain">maxvalue </code><code class="sql keyword">from</code> <code class="sql plain">table1</code></div><div class="line number39 index38 alt2"><code class="sql plain">最小：</code><code class="sql keyword">select</code> <code class="sql keyword">min</code><code class="sql plain">(field1) </code><code class="sql keyword">as</code> <code class="sql plain">minvalue </code><code class="sql keyword">from</code> <code class="sql plain">table1</code></div><div class="line number40 index39 alt1"><code class="sql plain">11、说明：几个高级查询运算词</code></div><div class="line number41 index40 alt2"><code class="sql plain">A： </code><code class="sql keyword">UNION</code> <code class="sql plain">运算符</code></div><div class="line number42 index41 alt1"><code class="sql keyword">UNION</code> <code class="sql plain">运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 </code><code class="sql color1">ALL</code> <code class="sql plain">随 </code><code class="sql keyword">UNION</code> <code class="sql plain">一起使用时（即 </code><code class="sql keyword">UNION</code> <code class="sql color1">ALL</code><code class="sql plain">），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。</code></div><div class="line number43 index42 alt2"><code class="sql plain">B： </code><code class="sql keyword">EXCEPT</code> <code class="sql plain">运算符</code></div><div class="line number44 index43 alt1"><code class="sql keyword">EXCEPT</code> <code class="sql plain">运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 </code><code class="sql color1">ALL</code> <code class="sql plain">随 </code><code class="sql keyword">EXCEPT</code> <code class="sql plain">一起使用时 (</code><code class="sql keyword">EXCEPT</code> <code class="sql color1">ALL</code><code class="sql plain">)，不消除重复行。</code></div><div class="line number45 index44 alt2"><code class="sql plain">C： </code><code class="sql keyword">INTERSECT</code> <code class="sql plain">运算符</code></div><div class="line number46 index45 alt1"><code class="sql keyword">INTERSECT</code> <code class="sql plain">运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 </code><code class="sql color1">ALL</code> <code class="sql plain">随 </code><code class="sql keyword">INTERSECT</code> <code class="sql plain">一起使用时 (</code><code class="sql keyword">INTERSECT</code> <code class="sql color1">ALL</code><code class="sql plain">)，不消除重复行。</code></div><div class="line number47 index46 alt2"><code class="sql plain">注：使用运算词的几个查询结果行必须是一致的。</code></div><div class="line number48 index47 alt1"><code class="sql plain">12、说明：使用外连接</code></div><div class="line number49 index48 alt2"><code class="sql plain">A、</code><code class="sql color2">left</code> <code class="sql plain">（</code><code class="sql color1">outer</code><code class="sql plain">） </code><code class="sql color1">join</code><code class="sql plain">：</code></div><div class="line number50 index49 alt1"><code class="sql plain">左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。</code></div><div class="line number51 index50 alt2"><code class="sql plain">SQL: </code><code class="sql keyword">select</code> <code class="sql plain">a.a, a.b, a.c, b.c, b.d, b.f </code><code class="sql keyword">from</code> <code class="sql plain">a </code><code class="sql color2">LEFT</code> <code class="sql keyword">OUT</code> <code class="sql color1">JOIN</code> <code class="sql plain">b </code><code class="sql keyword">ON</code> <code class="sql plain">a.a = b.c</code></div><div class="line number52 index51 alt1"><code class="sql plain">B：</code><code class="sql color2">right</code> <code class="sql plain">（</code><code class="sql color1">outer</code><code class="sql plain">） </code><code class="sql color1">join</code><code class="sql plain">:</code></div><div class="line number53 index52 alt2"><code class="sql plain">右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。</code></div><div class="line number54 index53 alt1"><code class="sql plain">C：</code><code class="sql keyword">full</code><code class="sql plain">/</code><code class="sql color1">cross</code> <code class="sql plain">（</code><code class="sql color1">outer</code><code class="sql plain">） </code><code class="sql color1">join</code><code class="sql plain">：</code></div><div class="line number55 index54 alt2"><code class="sql plain">全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。</code></div><div class="line number56 index55 alt1"><code class="sql plain">12、分组:</code><code class="sql keyword">Group</code> <code class="sql keyword">by</code><code class="sql plain">:</code></div><div class="line number57 index56 alt2"><code class="sql spaces">&nbsp;&nbsp;</code><code class="sql plain">一张表，一旦分组完成后，查询后只能得到组相关的信息。</code></div><div class="line number58 index57 alt1"><code class="sql spaces">&nbsp;</code><code class="sql plain">组相关的信息：（统计信息） </code><code class="sql color2">count</code><code class="sql plain">,</code><code class="sql color2">sum</code><code class="sql plain">,</code><code class="sql keyword">max</code><code class="sql plain">,</code><code class="sql keyword">min</code><code class="sql plain">,</code><code class="sql color2">avg</code>&nbsp; <code class="sql plain">分组的标准)</code></div><div class="line number59 index58 alt2"><code class="sql spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><code class="sql plain">在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据</code></div><div class="line number60 index59 alt1"><code class="sql spaces">&nbsp;</code><code class="sql plain">在selecte统计函数中的字段，不能和普通的字段放在一起；</code></div><div class="line number61 index60 alt2"><code class="sql plain">13、对数据库进行操作：</code></div><div class="line number62 index61 alt1"><code class="sql spaces">&nbsp;</code><code class="sql plain">分离数据库： sp_detach_db; 附加数据库：sp_attach_db 后接表明，附加需要完整的路径名</code></div><div class="line number63 index62 alt2"><code class="sql plain">14.如何修改数据库的名称:</code></div><div class="line number64 index63 alt1"><code class="sql plain">sp_renamedb </code><code class="sql string">'old_name'</code><code class="sql plain">, </code><code class="sql string">'new_name'</code></div><div class="line number65 index64 alt2">&nbsp;</div><div class="line number66 index65 alt1"><code class="sql plain">二、提升</code></div><div class="line number67 index66 alt2"><code class="sql plain">1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)</code></div><div class="line number68 index67 alt1"><code class="sql plain">法一：</code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">into</code> <code class="sql plain">b </code><code class="sql keyword">from</code> <code class="sql plain">a </code><code class="sql keyword">where</code> <code class="sql plain">1&lt;&gt;1（仅用于SQlServer）</code></div><div class="line number69 index68 alt2"><code class="sql plain">法二：</code><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">0 * </code><code class="sql keyword">into</code> <code class="sql plain">b </code><code class="sql keyword">from</code> <code class="sql plain">a</code></div><div class="line number70 index69 alt1"><code class="sql plain">2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)</code></div><div class="line number71 index70 alt2"><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">b(a, b, c) </code><code class="sql keyword">select</code> <code class="sql plain">d,e,f </code><code class="sql keyword">from</code> <code class="sql plain">b;</code></div><div class="line number72 index71 alt1"><code class="sql plain">3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)</code></div><div class="line number73 index72 alt2"><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">b(a, b, c) </code><code class="sql keyword">select</code> <code class="sql plain">d,e,f </code><code class="sql keyword">from</code> <code class="sql plain">b </code><code class="sql color1">in</code> <code class="sql plain">‘具体数据库’ </code><code class="sql keyword">where</code> <code class="sql plain">条件</code></div><div class="line number74 index73 alt1"><code class="sql plain">例子：..</code><code class="sql keyword">from</code> <code class="sql plain">b </code><code class="sql color1">in</code> <code class="sql string">'"&amp;Server.MapPath(".")&amp;"\data.mdb" &amp;"'</code> <code class="sql keyword">where</code><code class="sql plain">..</code></div><div class="line number75 index74 alt2"><code class="sql plain">4、说明：子查询(表名1：a 表名2：b)</code></div><div class="line number76 index75 alt1"><code class="sql keyword">select</code> <code class="sql plain">a,b,c </code><code class="sql keyword">from</code> <code class="sql plain">a </code><code class="sql keyword">where</code> <code class="sql plain">a </code><code class="sql color1">IN</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">d </code><code class="sql keyword">from</code> <code class="sql plain">b ) 或者: </code><code class="sql keyword">select</code> <code class="sql plain">a,b,c </code><code class="sql keyword">from</code> <code class="sql plain">a </code><code class="sql keyword">where</code> <code class="sql plain">a </code><code class="sql color1">IN</code> <code class="sql plain">(1,2,3)</code></div><div class="line number77 index76 alt2"><code class="sql plain">5、说明：显示文章、提交人和最后回复时间</code></div><div class="line number78 index77 alt1"><code class="sql keyword">select</code> <code class="sql plain">a.title,a.username,b.adddate </code><code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql plain">a,(</code><code class="sql keyword">select</code> <code class="sql keyword">max</code><code class="sql plain">(adddate) adddate </code><code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql keyword">where</code> <code class="sql keyword">table</code><code class="sql plain">.title=a.title) b</code></div><div class="line number79 index78 alt2"><code class="sql plain">6、说明：外连接查询(表名1：a 表名2：b)</code></div><div class="line number80 index79 alt1"><code class="sql keyword">select</code> <code class="sql plain">a.a, a.b, a.c, b.c, b.d, b.f </code><code class="sql keyword">from</code> <code class="sql plain">a </code><code class="sql color2">LEFT</code> <code class="sql keyword">OUT</code> <code class="sql color1">JOIN</code> <code class="sql plain">b </code><code class="sql keyword">ON</code> <code class="sql plain">a.a = b.c</code></div><div class="line number81 index80 alt2"><code class="sql plain">7、说明：在线视图查询(表名1：a )</code></div><div class="line number82 index81 alt1"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">(</code><code class="sql keyword">SELECT</code> <code class="sql plain">a,b,c </code><code class="sql keyword">FROM</code> <code class="sql plain">a) T </code><code class="sql keyword">where</code> <code class="sql plain">t.a &gt; 1;</code></div><div class="line number83 index82 alt2"><code class="sql plain">8、说明：</code><code class="sql color1">between</code><code class="sql plain">的用法,</code><code class="sql color1">between</code><code class="sql plain">限制查询数据范围时包括了边界值,</code><code class="sql color1">not</code> <code class="sql color1">between</code><code class="sql plain">不包括</code></div><div class="line number84 index83 alt1"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">table1 </code><code class="sql keyword">where</code> <code class="sql keyword">time</code> <code class="sql color1">between</code> <code class="sql plain">time1 </code><code class="sql color1">and</code> <code class="sql plain">time2</code></div><div class="line number85 index84 alt2"><code class="sql keyword">select</code> <code class="sql plain">a,b,c, </code><code class="sql keyword">from</code> <code class="sql plain">table1 </code><code class="sql keyword">where</code> <code class="sql plain">a </code><code class="sql color1">not</code> <code class="sql color1">between</code> <code class="sql plain">数值1 </code><code class="sql color1">and</code> <code class="sql plain">数值2</code></div><div class="line number86 index85 alt1"><code class="sql plain">9、说明：</code><code class="sql color1">in</code> <code class="sql plain">的使用方法</code></div><div class="line number87 index86 alt2"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">table1 </code><code class="sql keyword">where</code> <code class="sql plain">a [</code><code class="sql color1">not</code><code class="sql plain">] </code><code class="sql color1">in</code> <code class="sql plain">(‘值1’,’值2’,’值4’,’值6’)</code></div><div class="line number88 index87 alt1"><code class="sql plain">10、说明：两张关联表，删除主表中已经在副表中没有的信息</code></div><div class="line number89 index88 alt2"><code class="sql keyword">delete</code> <code class="sql keyword">from</code> <code class="sql plain">table1 </code><code class="sql keyword">where</code> <code class="sql color1">not</code> <code class="sql plain">exists ( </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">table2 </code><code class="sql keyword">where</code> <code class="sql plain">table1.field1=table2.field1 )</code></div><div class="line number90 index89 alt1"><code class="sql plain">11、说明：四表联查问题：</code></div><div class="line number91 index90 alt2"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">a </code><code class="sql color2">left</code> <code class="sql keyword">inner</code> <code class="sql color1">join</code> <code class="sql plain">b </code><code class="sql keyword">on</code> <code class="sql plain">a.a=b.b </code><code class="sql color2">right</code> <code class="sql keyword">inner</code> <code class="sql color1">join</code> <code class="sql plain">c </code><code class="sql keyword">on</code> <code class="sql plain">a.a=c.c </code><code class="sql keyword">inner</code> <code class="sql color1">join</code> <code class="sql plain">d </code><code class="sql keyword">on</code> <code class="sql plain">a.a=d.d </code><code class="sql keyword">where</code> <code class="sql plain">.....</code></div><div class="line number92 index91 alt1"><code class="sql plain">12、说明：日程安排提前五分钟提醒</code></div><div class="line number93 index92 alt2"><code class="sql plain">SQL: </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">日程安排 </code><code class="sql keyword">where</code> <code class="sql plain">datediff(</code><code class="sql string">'minute'</code><code class="sql plain">,f开始时间,getdate())&gt;5</code></div><div class="line number94 index93 alt1"><code class="sql plain">13、说明：一条sql 语句搞定数据库分页</code></div><div class="line number95 index94 alt2"><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">10 b.* </code><code class="sql keyword">from</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">20 主键字段,排序字段 </code><code class="sql keyword">from</code> <code class="sql plain">表名 </code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">排序字段 </code><code class="sql keyword">desc</code><code class="sql plain">) a,表名 b </code><code class="sql keyword">where</code> <code class="sql plain">b.主键字段 = a.主键字段 </code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">a.排序字段</code></div><div class="line number96 index95 alt1"><code class="sql plain">具体实现：</code></div><div class="line number97 index96 alt2"><code class="sql plain">关于数据库分页：</code></div><div class="line number98 index97 alt1"><code class="sql spaces">&nbsp;&nbsp;</code><code class="sql keyword">declare</code> <code class="sql plain">@start </code><code class="sql keyword">int</code><code class="sql plain">,@</code><code class="sql keyword">end</code> <code class="sql keyword">int</code></div><div class="line number99 index98 alt2"><code class="sql spaces">&nbsp;&nbsp;</code><code class="sql plain">@sql&nbsp; nvarchar(600)</code></div><div class="line number100 index99 alt1"><code class="sql spaces">&nbsp;&nbsp;</code><code class="sql keyword">set</code> <code class="sql plain">@sql=’</code><code class="sql keyword">select</code> <code class="sql keyword">top</code><code class="sql plain">’+str(@</code><code class="sql keyword">end</code><code class="sql plain">-@start+1)+’+</code><code class="sql keyword">from</code> <code class="sql plain">T </code><code class="sql keyword">where</code> <code class="sql plain">rid </code><code class="sql color1">not</code> <code class="sql color1">in</code><code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql keyword">top</code><code class="sql plain">’+str(@str-1)+’Rid </code><code class="sql keyword">from</code> <code class="sql plain">T </code><code class="sql keyword">where</code> <code class="sql plain">Rid&gt;-1)’</code></div><div class="line number101 index100 alt2"><code class="sql spaces">&nbsp;&nbsp;</code><code class="sql keyword">exec</code> <code class="sql plain">sp_executesql @sql</code></div><div class="line number102 index101 alt1">&nbsp;</div><div class="line number103 index102 alt2"><code class="sql plain">注意：在</code><code class="sql keyword">top</code><code class="sql plain">后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果</code><code class="sql keyword">top</code><code class="sql plain">后还有具体的字段，这样做是非常有好处的。因为这样可以避免 </code><code class="sql keyword">top</code><code class="sql plain">的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）</code></div><div class="line number104 index103 alt1"><code class="sql plain">14、说明：前10条记录</code></div><div class="line number105 index104 alt2"><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">10 * form table1 </code><code class="sql keyword">where</code> <code class="sql plain">范围</code></div><div class="line number106 index105 alt1"><code class="sql plain">15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)</code></div><div class="line number107 index106 alt2"><code class="sql keyword">select</code> <code class="sql plain">a,b,c </code><code class="sql keyword">from</code> <code class="sql plain">tablename ta </code><code class="sql keyword">where</code> <code class="sql plain">a=(</code><code class="sql keyword">select</code> <code class="sql keyword">max</code><code class="sql plain">(a) </code><code class="sql keyword">from</code> <code class="sql plain">tablename tb </code><code class="sql keyword">where</code> <code class="sql plain">tb.b=ta.b)</code></div><div class="line number108 index107 alt1"><code class="sql plain">16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表</code></div><div class="line number109 index108 alt2"><code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">a </code><code class="sql keyword">from</code> <code class="sql plain">tableA ) </code><code class="sql keyword">except</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">a </code><code class="sql keyword">from</code> <code class="sql plain">tableB) </code><code class="sql keyword">except</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">a </code><code class="sql keyword">from</code> <code class="sql plain">tableC)</code></div><div class="line number110 index109 alt1"><code class="sql plain">17、说明：随机取出10条数据</code></div><div class="line number111 index110 alt2"><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">10 * </code><code class="sql keyword">from</code> <code class="sql plain">tablename </code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">newid()</code></div><div class="line number112 index111 alt1"><code class="sql plain">18、说明：随机选择记录</code></div><div class="line number113 index112 alt2"><code class="sql keyword">select</code> <code class="sql plain">newid()</code></div><div class="line number114 index113 alt1"><code class="sql plain">19、说明：删除重复记录</code></div><div class="line number115 index114 alt2"><code class="sql plain">1),</code><code class="sql keyword">delete</code> <code class="sql keyword">from</code> <code class="sql plain">tablename </code><code class="sql keyword">where</code> <code class="sql plain">id </code><code class="sql color1">not</code> <code class="sql color1">in</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql keyword">max</code><code class="sql plain">(id) </code><code class="sql keyword">from</code> <code class="sql plain">tablename </code><code class="sql keyword">group</code> <code class="sql keyword">by</code> <code class="sql plain">col1,col2,...)</code></div><div class="line number116 index115 alt1"><code class="sql plain">2),</code><code class="sql keyword">select</code> <code class="sql keyword">distinct</code> <code class="sql plain">* </code><code class="sql keyword">into</code> <code class="sql keyword">temp</code> <code class="sql keyword">from</code> <code class="sql plain">tablename</code></div><div class="line number117 index116 alt2"><code class="sql spaces">&nbsp;&nbsp;</code><code class="sql keyword">delete</code> <code class="sql keyword">from</code> <code class="sql plain">tablename</code></div><div class="line number118 index117 alt1"><code class="sql spaces">&nbsp;&nbsp;</code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">tablename </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql keyword">temp</code></div><div class="line number119 index118 alt2"><code class="sql plain">评价： 这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作</code></div><div class="line number120 index119 alt1"><code class="sql plain">3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段</code></div><div class="line number121 index120 alt2"><code class="sql keyword">alter</code> <code class="sql keyword">table</code> <code class="sql plain">tablename</code></div><div class="line number122 index121 alt1"><code class="sql comments">--添加一个自增列</code></div><div class="line number123 index122 alt2"><code class="sql keyword">add</code>&nbsp; <code class="sql plain">column_b </code><code class="sql keyword">int</code> <code class="sql plain">identity(1,1)</code></div><div class="line number124 index123 alt1"><code class="sql spaces">&nbsp;</code><code class="sql keyword">delete</code> <code class="sql keyword">from</code> <code class="sql plain">tablename </code><code class="sql keyword">where</code> <code class="sql plain">column_b </code><code class="sql color1">not</code> <code class="sql color1">in</code><code class="sql plain">(</code></div><div class="line number125 index124 alt2"><code class="sql keyword">select</code> <code class="sql keyword">max</code><code class="sql plain">(column_b)&nbsp; </code><code class="sql keyword">from</code> <code class="sql plain">tablename </code><code class="sql keyword">group</code> <code class="sql keyword">by</code> <code class="sql plain">column1,column2,...)</code></div><div class="line number126 index125 alt1"><code class="sql keyword">alter</code> <code class="sql keyword">table</code> <code class="sql plain">tablename </code><code class="sql keyword">drop</code> <code class="sql keyword">column</code> <code class="sql plain">column_b</code></div><div class="line number127 index126 alt2"><code class="sql plain">20、说明：列出数据库里所有的表名</code></div><div class="line number128 index127 alt1"><code class="sql keyword">select</code> <code class="sql keyword">name</code> <code class="sql keyword">from</code> <code class="sql plain">sysobjects </code><code class="sql keyword">where</code> <code class="sql plain">type=</code><code class="sql string">'U'</code> <code class="sql plain">// U代表用户</code></div><div class="line number129 index128 alt2"><code class="sql plain">21、说明：列出表里的所有的列名</code></div><div class="line number130 index129 alt1"><code class="sql keyword">select</code> <code class="sql keyword">name</code> <code class="sql keyword">from</code> <code class="sql plain">syscolumns </code><code class="sql keyword">where</code> <code class="sql plain">id=object_id(</code><code class="sql string">'TableName'</code><code class="sql plain">)</code></div><div class="line number131 index130 alt2"><code class="sql plain">22、说明：列示type、vender、pcs字段，以type字段排列，</code><code class="sql color2">case</code><code class="sql plain">可以方便地实现多重选择，类似</code><code class="sql keyword">select</code> <code class="sql plain">中的</code><code class="sql color2">case</code><code class="sql plain">。</code></div><div class="line number132 index131 alt1"><code class="sql keyword">select</code> <code class="sql plain">type,</code><code class="sql color2">sum</code><code class="sql plain">(</code><code class="sql color2">case</code> <code class="sql plain">vender </code><code class="sql keyword">when</code> <code class="sql string">'A'</code> <code class="sql keyword">then</code> <code class="sql plain">pcs </code><code class="sql keyword">else</code> <code class="sql plain">0 </code><code class="sql keyword">end</code><code class="sql plain">),</code><code class="sql color2">sum</code><code class="sql plain">(</code><code class="sql color2">case</code> <code class="sql plain">vender </code><code class="sql keyword">when</code> <code class="sql string">'C'</code> <code class="sql keyword">then</code> <code class="sql plain">pcs </code><code class="sql keyword">else</code> <code class="sql plain">0 </code><code class="sql keyword">end</code><code class="sql plain">),</code><code class="sql color2">sum</code><code class="sql plain">(</code><code class="sql color2">case</code> <code class="sql plain">vender </code><code class="sql keyword">when</code> <code class="sql string">'B'</code> <code class="sql keyword">then</code> <code class="sql plain">pcs </code><code class="sql keyword">else</code> <code class="sql plain">0 </code><code class="sql keyword">end</code><code class="sql plain">) </code><code class="sql keyword">FROM</code> <code class="sql plain">tablename </code><code class="sql keyword">group</code> <code class="sql keyword">by</code> <code class="sql plain">type</code></div><div class="line number133 index132 alt2"><code class="sql plain">显示结果：</code></div><div class="line number134 index133 alt1"><code class="sql plain">type vender pcs</code></div><div class="line number135 index134 alt2"><code class="sql plain">电脑 A 1</code></div><div class="line number136 index135 alt1"><code class="sql plain">电脑 A 1</code></div><div class="line number137 index136 alt2"><code class="sql plain">光盘 B 2</code></div><div class="line number138 index137 alt1"><code class="sql plain">光盘 A 2</code></div><div class="line number139 index138 alt2"><code class="sql plain">手机 B 3</code></div><div class="line number140 index139 alt1"><code class="sql plain">手机 C 3</code></div><div class="line number141 index140 alt2"><code class="sql plain">23、说明：初始化表table1</code></div><div class="line number142 index141 alt1"><code class="sql keyword">TRUNCATE</code> <code class="sql keyword">TABLE</code> <code class="sql plain">table1</code></div><div class="line number143 index142 alt2"><code class="sql plain">24、说明：选择从10到15的记录</code></div><div class="line number144 index143 alt1"><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">5 * </code><code class="sql keyword">from</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">15 * </code><code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">id </code><code class="sql keyword">asc</code><code class="sql plain">) table_别名 </code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">id </code><code class="sql keyword">desc</code></div><div class="line number145 index144 alt2"><code class="sql plain">三、技巧</code></div><div class="line number146 index145 alt1"><code class="sql plain">1、1=1，1=2的使用，在SQL语句组合时用的较多</code></div><div class="line number147 index146 alt2"><code class="sql plain">“</code><code class="sql keyword">where</code> <code class="sql plain">1=1” 是表示选择全部&nbsp;&nbsp;&nbsp; “</code><code class="sql keyword">where</code> <code class="sql plain">1=2”全部不选，</code></div><div class="line number148 index147 alt1"><code class="sql plain">如：</code></div><div class="line number149 index148 alt2"><code class="sql plain">if @strWhere !=</code><code class="sql string">''</code></div><div class="line number150 index149 alt1"><code class="sql keyword">begin</code></div><div class="line number151 index150 alt2"><code class="sql keyword">set</code> <code class="sql plain">@strSQL = </code><code class="sql string">'select count(*) as Total from ['</code> <code class="sql plain">+ @tblName + </code><code class="sql string">'] where '</code> <code class="sql plain">+ @strWhere</code></div><div class="line number152 index151 alt1"><code class="sql keyword">end</code></div><div class="line number153 index152 alt2"><code class="sql keyword">else</code></div><div class="line number154 index153 alt1"><code class="sql keyword">begin</code></div><div class="line number155 index154 alt2"><code class="sql keyword">set</code> <code class="sql plain">@strSQL = </code><code class="sql string">'select count(*) as Total from ['</code> <code class="sql plain">+ @tblName + </code><code class="sql string">']'</code></div><div class="line number156 index155 alt1"><code class="sql keyword">end</code></div><div class="line number157 index156 alt2"><code class="sql plain">我们可以直接写成</code></div><div class="line number158 index157 alt1"><code class="sql plain">错误！未找到目录项。</code></div><div class="line number159 index158 alt2"><code class="sql keyword">set</code> <code class="sql plain">@strSQL = </code><code class="sql string">'select count(*) as Total from ['</code> <code class="sql plain">+ @tblName + </code><code class="sql string">'] where 1=1 安定 '</code><code class="sql plain">+ @strWhere 2、收缩数据库</code></div><div class="line number160 index159 alt1"><code class="sql comments">--重建索引</code></div><div class="line number161 index160 alt2"><code class="sql plain">DBCC REINDEX</code></div><div class="line number162 index161 alt1"><code class="sql plain">DBCC INDEXDEFRAG</code></div><div class="line number163 index162 alt2"><code class="sql comments">--收缩数据和日志</code></div><div class="line number164 index163 alt1"><code class="sql plain">DBCC SHRINKDB</code></div><div class="line number165 index164 alt2"><code class="sql plain">DBCC SHRINKFILE</code></div><div class="line number166 index165 alt1"><code class="sql plain">3、压缩数据库</code></div><div class="line number167 index166 alt2"><code class="sql plain">dbcc shrinkdatabase(dbname)</code></div><div class="line number168 index167 alt1"><code class="sql plain">4、转移数据库给新用户以已存在用户权限</code></div><div class="line number169 index168 alt2"><code class="sql keyword">exec</code> <code class="sql plain">sp_change_users_login </code><code class="sql string">'update_one'</code><code class="sql plain">,</code><code class="sql string">'newname'</code><code class="sql plain">,</code><code class="sql string">'oldname'</code></div><div class="line number170 index169 alt1"><code class="sql plain">go</code></div><div class="line number171 index170 alt2"><code class="sql plain">5、检查备份集</code></div><div class="line number172 index171 alt1"><code class="sql plain">RESTORE VERIFYONLY </code><code class="sql keyword">from</code> <code class="sql plain">disk=</code><code class="sql string">'E:\dvbbs.bak'</code></div><div class="line number173 index172 alt2"><code class="sql plain">6、修复数据库</code></div><div class="line number174 index173 alt1"><code class="sql keyword">ALTER</code> <code class="sql keyword">DATABASE</code> <code class="sql plain">[dvbbs] </code><code class="sql keyword">SET</code> <code class="sql plain">SINGLE_USER</code></div><div class="line number175 index174 alt2"><code class="sql plain">GO</code></div><div class="line number176 index175 alt1"><code class="sql plain">DBCC CHECKDB(</code><code class="sql string">'dvbbs'</code><code class="sql plain">,repair_allow_data_loss) </code><code class="sql keyword">WITH</code> <code class="sql plain">TABLOCK</code></div><div class="line number177 index176 alt2"><code class="sql plain">GO</code></div><div class="line number178 index177 alt1"><code class="sql keyword">ALTER</code> <code class="sql keyword">DATABASE</code> <code class="sql plain">[dvbbs] </code><code class="sql keyword">SET</code> <code class="sql plain">MULTI_USER</code></div><div class="line number179 index178 alt2"><code class="sql plain">GO</code></div><div class="line number180 index179 alt1"><code class="sql plain">7、日志清除</code></div><div class="line number181 index180 alt2"><code class="sql keyword">SET</code> <code class="sql plain">NOCOUNT </code><code class="sql keyword">ON</code></div><div class="line number182 index181 alt1"><code class="sql keyword">DECLARE</code> <code class="sql plain">@LogicalFileName sysname,</code></div><div class="line number183 index182 alt2"><code class="sql spaces">&nbsp;</code><code class="sql plain">@MaxMinutes </code><code class="sql keyword">INT</code><code class="sql plain">,</code></div><div class="line number184 index183 alt1"><code class="sql spaces">&nbsp;</code><code class="sql plain">@NewSize </code><code class="sql keyword">INT</code></div><div class="line number185 index184 alt2">&nbsp;</div><div class="line number186 index185 alt1"><code class="sql plain">USE tablename </code><code class="sql comments">-- 要操作的数据库名</code></div><div class="line number187 index186 alt2"><code class="sql keyword">SELECT</code>&nbsp; <code class="sql plain">@LogicalFileName = </code><code class="sql string">'tablename_log'</code><code class="sql plain">, </code><code class="sql comments">-- 日志文件名</code></div><div class="line number188 index187 alt1"><code class="sql plain">@MaxMinutes = 10, </code><code class="sql comments">-- Limit on time allowed to wrap log.</code></div><div class="line number189 index188 alt2"><code class="sql spaces">&nbsp;</code><code class="sql plain">@NewSize = 1&nbsp; </code><code class="sql comments">-- 你想设定的日志文件的大小(M)</code></div><div class="line number190 index189 alt1"><code class="sql plain">Setup / initialize</code></div><div class="line number191 index190 alt2"><code class="sql keyword">DECLARE</code> <code class="sql plain">@OriginalSize </code><code class="sql keyword">int</code></div><div class="line number192 index191 alt1"><code class="sql keyword">SELECT</code> <code class="sql plain">@OriginalSize = </code><code class="sql keyword">size</code></div><div class="line number193 index192 alt2"><code class="sql spaces">&nbsp;</code><code class="sql keyword">FROM</code> <code class="sql plain">sysfiles</code></div><div class="line number194 index193 alt1"><code class="sql spaces">&nbsp;</code><code class="sql keyword">WHERE</code> <code class="sql keyword">name</code> <code class="sql plain">= @LogicalFileName</code></div><div class="line number195 index194 alt2"><code class="sql keyword">SELECT</code> <code class="sql string">'Original Size of '</code> <code class="sql plain">+ db_name() + </code><code class="sql string">' LOG is '</code> <code class="sql plain">+</code></div><div class="line number196 index195 alt1"><code class="sql spaces">&nbsp;</code><code class="sql color2">CONVERT</code><code class="sql plain">(</code><code class="sql keyword">VARCHAR</code><code class="sql plain">(30),@OriginalSize) + </code><code class="sql string">' 8K pages or '</code> <code class="sql plain">+</code></div><div class="line number197 index196 alt2"><code class="sql spaces">&nbsp;</code><code class="sql color2">CONVERT</code><code class="sql plain">(</code><code class="sql keyword">VARCHAR</code><code class="sql plain">(30),(@OriginalSize*8/1024)) + </code><code class="sql string">'MB'</code></div><div class="line number198 index197 alt1"><code class="sql spaces">&nbsp;</code><code class="sql keyword">FROM</code> <code class="sql plain">sysfiles</code></div><div class="line number199 index198 alt2"><code class="sql spaces">&nbsp;</code><code class="sql keyword">WHERE</code> <code class="sql keyword">name</code> <code class="sql plain">= @LogicalFileName</code></div><div class="line number200 index199 alt1"><code class="sql keyword">CREATE</code> <code class="sql keyword">TABLE</code> <code class="sql plain">DummyTrans</code></div><div class="line number201 index200 alt2"><code class="sql spaces">&nbsp;</code><code class="sql plain">(DummyColumn </code><code class="sql keyword">char</code> <code class="sql plain">(8000) </code><code class="sql color1">not</code> <code class="sql color1">null</code><code class="sql plain">)</code></div><div class="line number202 index201 alt1">&nbsp;</div><div class="line number203 index202 alt2"><code class="sql keyword">DECLARE</code> <code class="sql plain">@Counter&nbsp;&nbsp;&nbsp; </code><code class="sql keyword">INT</code><code class="sql plain">,</code></div><div class="line number204 index203 alt1"><code class="sql spaces">&nbsp;</code><code class="sql plain">@StartTime DATETIME,</code></div><div class="line number205 index204 alt2"><code class="sql spaces">&nbsp;</code><code class="sql plain">@TruncLog&nbsp;&nbsp; </code><code class="sql keyword">VARCHAR</code><code class="sql plain">(255)</code></div><div class="line number206 index205 alt1"><code class="sql keyword">SELECT</code> <code class="sql plain">@StartTime = GETDATE(),</code></div><div class="line number207 index206 alt2"><code class="sql spaces">&nbsp;</code><code class="sql plain">@TruncLog = </code><code class="sql string">'BACKUP LOG '</code> <code class="sql plain">+ db_name() + </code><code class="sql string">' WITH TRUNCATE_ONLY'</code></div><div class="line number208 index207 alt1"><code class="sql plain">DBCC SHRINKFILE (@LogicalFileName, @NewSize)</code></div><div class="line number209 index208 alt2"><code class="sql keyword">EXEC</code> <code class="sql plain">(@TruncLog)</code></div><div class="line number210 index209 alt1"><code class="sql comments">-- Wrap the log if necessary.</code></div><div class="line number211 index210 alt2"><code class="sql plain">WHILE @MaxMinutes &gt; DATEDIFF (mi, @StartTime, GETDATE()) </code><code class="sql comments">-- time has not expired</code></div><div class="line number212 index211 alt1"><code class="sql spaces">&nbsp;</code><code class="sql color1">AND</code> <code class="sql plain">@OriginalSize = (</code><code class="sql keyword">SELECT</code> <code class="sql keyword">size</code> <code class="sql keyword">FROM</code> <code class="sql plain">sysfiles </code><code class="sql keyword">WHERE</code> <code class="sql keyword">name</code> <code class="sql plain">= @LogicalFileName) </code></div><div class="line number213 index212 alt2"><code class="sql spaces">&nbsp;</code><code class="sql color1">AND</code> <code class="sql plain">(@OriginalSize * 8 /1024) &gt; @NewSize </code></div><div class="line number214 index213 alt1"><code class="sql spaces">&nbsp;</code><code class="sql keyword">BEGIN</code> <code class="sql comments">-- Outer loop.</code></div><div class="line number215 index214 alt2"><code class="sql keyword">SELECT</code> <code class="sql plain">@Counter = 0</code></div><div class="line number216 index215 alt1"><code class="sql spaces">&nbsp;</code><code class="sql plain">WHILE&nbsp;&nbsp; ((@Counter &lt; @OriginalSize / 16) </code><code class="sql color1">AND</code> <code class="sql plain">(@Counter &lt; 50000))</code></div><div class="line number217 index216 alt2"><code class="sql spaces">&nbsp;</code><code class="sql keyword">BEGIN</code> <code class="sql comments">-- update</code></div><div class="line number218 index217 alt1"><code class="sql spaces">&nbsp;</code><code class="sql keyword">INSERT</code> <code class="sql plain">DummyTrans </code><code class="sql keyword">VALUES</code> <code class="sql plain">(</code><code class="sql string">'Fill Log'</code><code class="sql plain">) </code><code class="sql keyword">DELETE</code> <code class="sql plain">DummyTrans</code></div><div class="line number219 index218 alt2"><code class="sql spaces">&nbsp;</code><code class="sql keyword">SELECT</code> <code class="sql plain">@Counter = @Counter + 1</code></div><div class="line number220 index219 alt1"><code class="sql spaces">&nbsp;</code><code class="sql keyword">END</code></div><div class="line number221 index220 alt2"><code class="sql spaces">&nbsp;</code><code class="sql keyword">EXEC</code> <code class="sql plain">(@TruncLog) </code></div><div class="line number222 index221 alt1"><code class="sql spaces">&nbsp;</code><code class="sql keyword">END</code></div><div class="line number223 index222 alt2"><code class="sql keyword">SELECT</code> <code class="sql string">'Final Size of '</code> <code class="sql plain">+ db_name() + </code><code class="sql string">' LOG is '</code> <code class="sql plain">+</code></div><div class="line number224 index223 alt1"><code class="sql spaces">&nbsp;</code><code class="sql color2">CONVERT</code><code class="sql plain">(</code><code class="sql keyword">VARCHAR</code><code class="sql plain">(30),</code><code class="sql keyword">size</code><code class="sql plain">) + </code><code class="sql string">' 8K pages or '</code> <code class="sql plain">+</code></div><div class="line number225 index224 alt2"><code class="sql spaces">&nbsp;</code><code class="sql color2">CONVERT</code><code class="sql plain">(</code><code class="sql keyword">VARCHAR</code><code class="sql plain">(30),(</code><code class="sql keyword">size</code><code class="sql plain">*8/1024)) + </code><code class="sql string">'MB'</code></div><div class="line number226 index225 alt1"><code class="sql spaces">&nbsp;</code><code class="sql keyword">FROM</code> <code class="sql plain">sysfiles</code></div><div class="line number227 index226 alt2"><code class="sql spaces">&nbsp;</code><code class="sql keyword">WHERE</code> <code class="sql keyword">name</code> <code class="sql plain">= @LogicalFileName</code></div><div class="line number228 index227 alt1"><code class="sql keyword">DROP</code> <code class="sql keyword">TABLE</code> <code class="sql plain">DummyTrans</code></div><div class="line number229 index228 alt2"><code class="sql keyword">SET</code> <code class="sql plain">NOCOUNT </code><code class="sql keyword">OFF</code></div><div class="line number230 index229 alt1"><code class="sql plain">8、说明：更改某个表</code></div><div class="line number231 index230 alt2"><code class="sql keyword">exec</code> <code class="sql plain">sp_changeobjectowner </code><code class="sql string">'tablename'</code><code class="sql plain">,</code><code class="sql string">'dbo'</code></div><div class="line number232 index231 alt1"><code class="sql plain">9、存储更改全部表</code></div><div class="line number233 index232 alt2"><code class="sql keyword">CREATE</code> <code class="sql keyword">PROCEDURE</code> <code class="sql plain">dbo.User_ChangeObjectOwnerBatch</code></div><div class="line number234 index233 alt1"><code class="sql plain">@OldOwner </code><code class="sql keyword">as</code> <code class="sql plain">NVARCHAR(128),</code></div><div class="line number235 index234 alt2"><code class="sql plain">@NewOwner </code><code class="sql keyword">as</code> <code class="sql plain">NVARCHAR(128)</code></div><div class="line number236 index235 alt1"><code class="sql keyword">AS</code></div><div class="line number237 index236 alt2"><code class="sql keyword">DECLARE</code> <code class="sql plain">@</code><code class="sql keyword">Name</code>&nbsp;&nbsp;&nbsp; <code class="sql keyword">as</code> <code class="sql plain">NVARCHAR(128)</code></div><div class="line number238 index237 alt1"><code class="sql keyword">DECLARE</code> <code class="sql plain">@Owner&nbsp;&nbsp; </code><code class="sql keyword">as</code> <code class="sql plain">NVARCHAR(128)</code></div><div class="line number239 index238 alt2"><code class="sql keyword">DECLARE</code> <code class="sql plain">@OwnerName&nbsp;&nbsp; </code><code class="sql keyword">as</code> <code class="sql plain">NVARCHAR(128)</code></div><div class="line number240 index239 alt1"><code class="sql keyword">DECLARE</code> <code class="sql plain">curObject </code><code class="sql keyword">CURSOR</code> <code class="sql keyword">FOR</code></div><div class="line number241 index240 alt2"><code class="sql keyword">select</code> <code class="sql string">'Name'</code>&nbsp;&nbsp;&nbsp; <code class="sql plain">= </code><code class="sql keyword">name</code><code class="sql plain">,</code></div><div class="line number242 index241 alt1"><code class="sql spaces">&nbsp;&nbsp;&nbsp;</code><code class="sql string">'Owner'</code>&nbsp;&nbsp;&nbsp; <code class="sql plain">= user_name(uid)</code></div><div class="line number243 index242 alt2"><code class="sql keyword">from</code> <code class="sql plain">sysobjects</code></div><div class="line number244 index243 alt1"><code class="sql keyword">where</code> <code class="sql plain">user_name(uid)=@OldOwner</code></div><div class="line number245 index244 alt2"><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql keyword">name</code></div><div class="line number246 index245 alt1"><code class="sql keyword">OPEN</code>&nbsp;&nbsp; <code class="sql plain">curObject</code></div><div class="line number247 index246 alt2"><code class="sql keyword">FETCH</code> <code class="sql keyword">NEXT</code> <code class="sql keyword">FROM</code> <code class="sql plain">curObject </code><code class="sql keyword">INTO</code> <code class="sql plain">@</code><code class="sql keyword">Name</code><code class="sql plain">, @Owner</code></div><div class="line number248 index247 alt1"><code class="sql plain">WHILE(@@FETCH_STATUS=0)</code></div><div class="line number249 index248 alt2"><code class="sql keyword">BEGIN</code>&nbsp;&nbsp;&nbsp;</div><div class="line number250 index249 alt1"><code class="sql plain">if @Owner=@OldOwner</code></div><div class="line number251 index250 alt2"><code class="sql keyword">begin</code></div><div class="line number252 index251 alt1"><code class="sql spaces">&nbsp;&nbsp;&nbsp;</code><code class="sql keyword">set</code> <code class="sql plain">@OwnerName = @OldOwner + </code><code class="sql string">'.'</code> <code class="sql plain">+ rtrim(@</code><code class="sql keyword">Name</code><code class="sql plain">)</code></div><div class="line number253 index252 alt2"><code class="sql spaces">&nbsp;&nbsp;&nbsp;</code><code class="sql keyword">exec</code> <code class="sql plain">sp_changeobjectowner @OwnerName, @NewOwner</code></div><div class="line number254 index253 alt1"><code class="sql keyword">end</code></div><div class="line number255 index254 alt2"><code class="sql comments">-- select @name,@NewOwner,@OldOwner</code></div><div class="line number256 index255 alt1"><code class="sql keyword">FETCH</code> <code class="sql keyword">NEXT</code> <code class="sql keyword">FROM</code> <code class="sql plain">curObject </code><code class="sql keyword">INTO</code> <code class="sql plain">@</code><code class="sql keyword">Name</code><code class="sql plain">, @Owner</code></div><div class="line number257 index256 alt2"><code class="sql keyword">END</code></div><div class="line number258 index257 alt1"><code class="sql keyword">close</code> <code class="sql plain">curObject</code></div><div class="line number259 index258 alt2"><code class="sql keyword">deallocate</code> <code class="sql plain">curObject</code></div><div class="line number260 index259 alt1"><code class="sql plain">GO</code></div><div class="line number261 index260 alt2">&nbsp;</div><div class="line number262 index261 alt1"><code class="sql plain">10、SQL SERVER中直接循环写入数据</code></div><div class="line number263 index262 alt2"><code class="sql keyword">declare</code> <code class="sql plain">@i </code><code class="sql keyword">int</code></div><div class="line number264 index263 alt1"><code class="sql keyword">set</code> <code class="sql plain">@i=1</code></div><div class="line number265 index264 alt2"><code class="sql plain">while @i&lt;30</code></div><div class="line number266 index265 alt1"><code class="sql keyword">begin</code></div><div class="line number267 index266 alt2"><code class="sql spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">test (userid) </code><code class="sql keyword">values</code><code class="sql plain">(@i)</code></div><div class="line number268 index267 alt1"><code class="sql spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><code class="sql keyword">set</code> <code class="sql plain">@i=@i+1</code></div><div class="line number269 index268 alt2"><code class="sql keyword">end</code></div><div class="line number270 index269 alt1"><code class="sql plain">案例：</code></div><div class="line number271 index270 alt2"><code class="sql plain">有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:</code></div><div class="line number272 index271 alt1"><code class="sql spaces">&nbsp;</code><code class="sql keyword">Name</code>&nbsp;&nbsp;&nbsp;&nbsp; <code class="sql plain">score</code></div><div class="line number273 index272 alt2"><code class="sql spaces">&nbsp;</code><code class="sql plain">Zhangshan 80</code></div><div class="line number274 index273 alt1"><code class="sql spaces">&nbsp;</code><code class="sql plain">Lishi&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 59</code></div><div class="line number275 index274 alt2"><code class="sql spaces">&nbsp;</code><code class="sql plain">Wangwu&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50</code></div><div class="line number276 index275 alt1"><code class="sql spaces">&nbsp;</code><code class="sql plain">Songquan 69</code></div><div class="line number277 index276 alt2"><code class="sql plain">while((</code><code class="sql keyword">select</code> <code class="sql keyword">min</code><code class="sql plain">(score) </code><code class="sql keyword">from</code> <code class="sql plain">tb_table)&lt;60)</code></div><div class="line number278 index277 alt1"><code class="sql keyword">begin</code></div><div class="line number279 index278 alt2"><code class="sql keyword">update</code> <code class="sql plain">tb_table </code><code class="sql keyword">set</code> <code class="sql plain">score =score*1.01</code></div><div class="line number280 index279 alt1"><code class="sql keyword">where</code> <code class="sql plain">score&lt;60</code></div><div class="line number281 index280 alt2"><code class="sql plain">if&nbsp; (</code><code class="sql keyword">select</code> <code class="sql keyword">min</code><code class="sql plain">(score) </code><code class="sql keyword">from</code> <code class="sql plain">tb_table)&gt;60</code></div><div class="line number282 index281 alt1"><code class="sql spaces">&nbsp;&nbsp;</code><code class="sql plain">break</code></div><div class="line number283 index282 alt2"><code class="sql spaces">&nbsp;</code><code class="sql keyword">else</code></div><div class="line number284 index283 alt1"><code class="sql spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><code class="sql keyword">continue</code></div><div class="line number285 index284 alt2"><code class="sql keyword">end</code></div><div class="line number286 index285 alt1">&nbsp;</div><div class="line number287 index286 alt2"><code class="sql plain">数据开发-经典</code></div><div class="line number288 index287 alt1">&nbsp;</div><div class="line number289 index288 alt2"><code class="sql plain">1.按姓氏笔画排序:</code></div><div class="line number290 index289 alt1"><code class="sql keyword">Select</code> <code class="sql plain">* </code><code class="sql keyword">From</code> <code class="sql plain">TableName </code><code class="sql keyword">Order</code> <code class="sql keyword">By</code> <code class="sql plain">CustomerName </code><code class="sql keyword">Collate</code> <code class="sql plain">Chinese_PRC_Stroke_ci_as //从少到多</code></div><div class="line number291 index290 alt2"><code class="sql plain">2.数据库加密:</code></div><div class="line number292 index291 alt1"><code class="sql keyword">select</code> <code class="sql plain">encrypt(</code><code class="sql string">'原始密码'</code><code class="sql plain">)</code></div><div class="line number293 index292 alt2"><code class="sql keyword">select</code> <code class="sql plain">pwdencrypt(</code><code class="sql string">'原始密码'</code><code class="sql plain">)</code></div><div class="line number294 index293 alt1"><code class="sql keyword">select</code> <code class="sql plain">pwdcompare(</code><code class="sql string">'原始密码'</code><code class="sql plain">,</code><code class="sql string">'加密后密码'</code><code class="sql plain">) = 1</code><code class="sql comments">--相同；否则不相同 encrypt('原始密码')</code></div><div class="line number295 index294 alt2"><code class="sql keyword">select</code> <code class="sql plain">pwdencrypt(</code><code class="sql string">'原始密码'</code><code class="sql plain">)</code></div><div class="line number296 index295 alt1"><code class="sql keyword">select</code> <code class="sql plain">pwdcompare(</code><code class="sql string">'原始密码'</code><code class="sql plain">,</code><code class="sql string">'加密后密码'</code><code class="sql plain">) = 1</code><code class="sql comments">--相同；否则不相同</code></div><div class="line number297 index296 alt2"><code class="sql plain">3.取回表中字段:</code></div><div class="line number298 index297 alt1"><code class="sql keyword">declare</code> <code class="sql plain">@list </code><code class="sql keyword">varchar</code><code class="sql plain">(1000),</code></div><div class="line number299 index298 alt2"><code class="sql plain">@sql nvarchar(1000)</code></div><div class="line number300 index299 alt1"><code class="sql keyword">select</code> <code class="sql plain">@list=@list+</code><code class="sql string">','</code><code class="sql plain">+b.</code><code class="sql keyword">name</code> <code class="sql keyword">from</code> <code class="sql plain">sysobjects a,syscolumns b </code><code class="sql keyword">where</code> <code class="sql plain">a.id=b.id </code><code class="sql color1">and</code> <code class="sql plain">a.</code><code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">'表A'</code></div><div class="line number301 index300 alt2"><code class="sql keyword">set</code> <code class="sql plain">@sql=</code><code class="sql string">'select '</code><code class="sql plain">+</code><code class="sql color2">right</code><code class="sql plain">(@list,len(@list)-1)+</code><code class="sql string">' from 表A'</code></div><div class="line number302 index301 alt1"><code class="sql keyword">exec</code> <code class="sql plain">(@sql)</code></div><div class="line number303 index302 alt2"><code class="sql plain">4.查看硬盘分区:</code></div><div class="line number304 index303 alt1"><code class="sql keyword">EXEC</code> <code class="sql plain">master..xp_fixeddrives</code></div><div class="line number305 index304 alt2"><code class="sql plain">5.比较A,B表是否相等:</code></div><div class="line number306 index305 alt1"><code class="sql plain">if (</code><code class="sql keyword">select</code> <code class="sql plain">checksum_agg(binary_checksum(*)) </code><code class="sql keyword">from</code> <code class="sql plain">A)</code></div><div class="line number307 index306 alt2"><code class="sql spaces">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code class="sql plain">=</code></div><div class="line number308 index307 alt1"><code class="sql spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">checksum_agg(binary_checksum(*)) </code><code class="sql keyword">from</code> <code class="sql plain">B)</code></div><div class="line number309 index308 alt2"><code class="sql plain">print </code><code class="sql string">'相等'</code></div><div class="line number310 index309 alt1"><code class="sql keyword">else</code></div><div class="line number311 index310 alt2"><code class="sql plain">print </code><code class="sql string">'不相等'</code></div><div class="line number312 index311 alt1"><code class="sql plain">6.杀掉所有的事件探察器进程:</code></div><div class="line number313 index312 alt2"><code class="sql keyword">DECLARE</code> <code class="sql plain">hcforeach </code><code class="sql keyword">CURSOR</code> <code class="sql keyword">GLOBAL</code> <code class="sql keyword">FOR</code> <code class="sql keyword">SELECT</code> <code class="sql string">'kill '</code><code class="sql plain">+RTRIM(spid) </code><code class="sql keyword">FROM</code> <code class="sql plain">master.dbo.sysprocesses</code></div><div class="line number314 index313 alt1"><code class="sql keyword">WHERE</code> <code class="sql plain">program_name </code><code class="sql color1">IN</code><code class="sql plain">(</code><code class="sql string">'SQL profiler'</code><code class="sql plain">,N</code><code class="sql string">'SQL 事件探查器'</code><code class="sql plain">)</code></div><div class="line number315 index314 alt2"><code class="sql keyword">EXEC</code> <code class="sql plain">sp_msforeach_worker </code><code class="sql string">'?'</code></div><div class="line number316 index315 alt1"><code class="sql plain">7.记录搜索:</code></div><div class="line number317 index316 alt2"><code class="sql plain">开头到N条记录</code></div><div class="line number318 index317 alt1"><code class="sql keyword">Select</code> <code class="sql keyword">Top</code> <code class="sql plain">N * </code><code class="sql keyword">From</code> <code class="sql plain">表</code></div><div class="line number319 index318 alt2"><code class="sql comments">-------------------------------</code></div><div class="line number320 index319 alt1"><code class="sql plain">N到M条记录(要有主索引ID)</code></div><div class="line number321 index320 alt2"><code class="sql keyword">Select</code> <code class="sql keyword">Top</code> <code class="sql plain">M-N * </code><code class="sql keyword">From</code> <code class="sql plain">表 </code><code class="sql keyword">Where</code> <code class="sql plain">ID </code><code class="sql color1">in</code> <code class="sql plain">(</code><code class="sql keyword">Select</code> <code class="sql keyword">Top</code> <code class="sql plain">M ID </code><code class="sql keyword">From</code> <code class="sql plain">表) </code><code class="sql keyword">Order</code> <code class="sql keyword">by</code> <code class="sql plain">ID&nbsp;&nbsp; </code><code class="sql keyword">Desc</code></div><div class="line number322 index321 alt1"><code class="sql comments">----------------------------------</code></div><div class="line number323 index322 alt2"><code class="sql plain">N到结尾记录</code></div><div class="line number324 index323 alt1"><code class="sql keyword">Select</code> <code class="sql keyword">Top</code> <code class="sql plain">N * </code><code class="sql keyword">From</code> <code class="sql plain">表 </code><code class="sql keyword">Order</code> <code class="sql keyword">by</code> <code class="sql plain">ID </code><code class="sql keyword">Desc</code></div><div class="line number325 index324 alt2"><code class="sql plain">案例</code></div><div class="line number326 index325 alt1"><code class="sql plain">例如1：一张表有一万多条记录，表的第一个字段 RecID 是自增长字段， 写一个SQL语句，找出表的第31到第40个记录。</code></div><div class="line number327 index326 alt2"><code class="sql spaces">&nbsp;</code><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">10 recid </code><code class="sql keyword">from</code> <code class="sql plain">A </code><code class="sql keyword">where</code> <code class="sql plain">recid </code><code class="sql color1">not</code>&nbsp; <code class="sql color1">in</code><code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">30 recid </code><code class="sql keyword">from</code> <code class="sql plain">A)</code></div><div class="line number328 index327 alt1"><code class="sql plain">分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。</code></div><div class="line number329 index328 alt2"><code class="sql spaces">&nbsp;</code><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">10 recid </code><code class="sql keyword">from</code> <code class="sql plain">A </code><code class="sql keyword">where</code><code class="sql plain">……是从索引中查找，而后面的</code><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">30 recid </code><code class="sql keyword">from</code> <code class="sql plain">A则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。</code></div><div class="line number330 index329 alt1"><code class="sql plain">解决方案</code></div><div class="line number331 index330 alt2"><code class="sql plain">1， 用</code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">30 recid </code><code class="sql keyword">from</code> <code class="sql plain">A </code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">ricid 如果该字段不是自增长，就会出现问题</code></div><div class="line number332 index331 alt1"><code class="sql plain">2， 在那个子查询中也加条件：</code><code class="sql keyword">select</code> <code class="sql keyword">top</code> <code class="sql plain">30 recid </code><code class="sql keyword">from</code> <code class="sql plain">A </code><code class="sql keyword">where</code> <code class="sql plain">recid&gt;-1</code></div><div class="line number333 index332 alt2"><code class="sql plain">例2：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构。</code></div><div class="line number334 index333 alt1"><code class="sql keyword">set</code> <code class="sql plain">@s = </code><code class="sql string">'select top 1 * from T&nbsp;&nbsp; where pid not in (select top '</code> <code class="sql plain">+ str(@</code><code class="sql color2">count</code><code class="sql plain">-1) + </code><code class="sql string">' pid&nbsp; from&nbsp; T)'</code></div><div class="line number335 index334 alt2"><code class="sql plain">print @s&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code><code class="sql keyword">exec</code>&nbsp; <code class="sql plain">sp_executesql&nbsp; @s</code></div><div class="line number336 index335 alt1"><code class="sql plain">9：获取当前数据库中的所有用户表</code></div><div class="line number337 index336 alt2"><code class="sql keyword">select</code> <code class="sql keyword">Name</code> <code class="sql keyword">from</code> <code class="sql plain">sysobjects </code><code class="sql keyword">where</code> <code class="sql plain">xtype=</code><code class="sql string">'u'</code> <code class="sql color1">and</code> <code class="sql plain">status&gt;=0</code></div><div class="line number338 index337 alt1"><code class="sql plain">10：获取某一个表的所有字段</code></div><div class="line number339 index338 alt2"><code class="sql keyword">select</code> <code class="sql keyword">name</code> <code class="sql keyword">from</code> <code class="sql plain">syscolumns </code><code class="sql keyword">where</code> <code class="sql plain">id=object_id(</code><code class="sql string">'表名'</code><code class="sql plain">)</code></div><div class="line number340 index339 alt1"><code class="sql keyword">select</code> <code class="sql keyword">name</code> <code class="sql keyword">from</code> <code class="sql plain">syscolumns </code><code class="sql keyword">where</code> <code class="sql plain">id </code><code class="sql color1">in</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">id </code><code class="sql keyword">from</code> <code class="sql plain">sysobjects </code><code class="sql keyword">where</code> <code class="sql plain">type = </code><code class="sql string">'u'</code> <code class="sql color1">and</code> <code class="sql keyword">name</code> <code class="sql plain">= </code><code class="sql string">'表名'</code><code class="sql plain">)</code></div><div class="line number341 index340 alt2"><code class="sql plain">两种方式的效果相同</code></div><div class="line number342 index341 alt1"><code class="sql plain">11：查看与某一个表相关的视图、存储过程、函数</code></div><div class="line number343 index342 alt2"><code class="sql keyword">select</code> <code class="sql plain">a.* </code><code class="sql keyword">from</code> <code class="sql plain">sysobjects a, syscomments b </code><code class="sql keyword">where</code> <code class="sql plain">a.id = b.id </code><code class="sql color1">and</code> <code class="sql plain">b.text </code><code class="sql color1">like</code> <code class="sql string">'%表名%'</code></div><div class="line number344 index343 alt1"><code class="sql plain">12：查看当前数据库中所有存储过程</code></div><div class="line number345 index344 alt2"><code class="sql keyword">select</code> <code class="sql keyword">name</code> <code class="sql keyword">as</code> <code class="sql plain">存储过程名称 </code><code class="sql keyword">from</code> <code class="sql plain">sysobjects </code><code class="sql keyword">where</code> <code class="sql plain">xtype=</code><code class="sql string">'P'</code></div><div class="line number346 index345 alt1"><code class="sql plain">13：查询用户创建的所有数据库</code></div><div class="line number347 index346 alt2"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">master..sysdatabases D </code><code class="sql keyword">where</code> <code class="sql plain">sid </code><code class="sql color1">not</code> <code class="sql color1">in</code><code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">sid </code><code class="sql keyword">from</code> <code class="sql plain">master..syslogins </code><code class="sql keyword">where</code> <code class="sql keyword">name</code><code class="sql plain">=</code><code class="sql string">'sa'</code><code class="sql plain">)</code></div><div class="line number348 index347 alt1"><code class="sql plain">或者</code></div><div class="line number349 index348 alt2"><code class="sql keyword">select</code> <code class="sql plain">dbid, </code><code class="sql keyword">name</code> <code class="sql keyword">AS</code> <code class="sql plain">DB_NAME </code><code class="sql keyword">from</code> <code class="sql plain">master..sysdatabases </code><code class="sql keyword">where</code> <code class="sql plain">sid &lt;&gt; 0x01</code></div><div class="line number350 index349 alt1"><code class="sql plain">14：查询某一个表的字段和数据类型</code></div><div class="line number351 index350 alt2"><code class="sql keyword">select</code> <code class="sql plain">column_name,data_type </code><code class="sql keyword">from</code> <code class="sql plain">information_schema.columns</code></div><div class="line number352 index351 alt1"><code class="sql keyword">where</code> <code class="sql plain">table_name = </code><code class="sql string">'表名'</code></div><div class="line number353 index352 alt2"><code class="sql plain">15：不同服务器数据库之间的数据操作</code></div><div class="line number354 index353 alt1"><code class="sql comments">--创建链接服务器</code></div><div class="line number355 index354 alt2"><code class="sql keyword">exec</code> <code class="sql plain">sp_addlinkedserver&nbsp;&nbsp; </code><code class="sql string">'ITSV '</code><code class="sql plain">, </code><code class="sql string">' '</code><code class="sql plain">, </code><code class="sql string">'SQLOLEDB '</code><code class="sql plain">, </code><code class="sql string">'远程服务器名或ip地址 '</code></div><div class="line number356 index355 alt1"><code class="sql keyword">exec</code> <code class="sql plain">sp_addlinkedsrvlogin&nbsp; </code><code class="sql string">'ITSV '</code><code class="sql plain">, </code><code class="sql string">'false '</code><code class="sql plain">,</code><code class="sql color1">null</code><code class="sql plain">, </code><code class="sql string">'用户名 '</code><code class="sql plain">, </code><code class="sql string">'密码 '</code></div><div class="line number357 index356 alt2"><code class="sql comments">--查询示例</code></div><div class="line number358 index357 alt1"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">ITSV.数据库名.dbo.表名</code></div><div class="line number359 index358 alt2"><code class="sql comments">--导入示例</code></div><div class="line number360 index359 alt1"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">into</code> <code class="sql plain">表 </code><code class="sql keyword">from</code> <code class="sql plain">ITSV.数据库名.dbo.表名</code></div><div class="line number361 index360 alt2"><code class="sql comments">--以后不再使用时删除链接服务器</code></div><div class="line number362 index361 alt1"><code class="sql keyword">exec</code> <code class="sql plain">sp_dropserver&nbsp; </code><code class="sql string">'ITSV '</code><code class="sql plain">, </code><code class="sql string">'droplogins '</code></div><div class="line number363 index362 alt2">&nbsp;</div><div class="line number364 index363 alt1"><code class="sql comments">--连接远程/局域网数据(openrowset/openquery/opendatasource)</code></div><div class="line number365 index364 alt2"><code class="sql comments">--1、openrowset</code></div><div class="line number366 index365 alt1"><code class="sql comments">--查询示例</code></div><div class="line number367 index366 alt2"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">openrowset( </code><code class="sql string">'SQLOLEDB '</code><code class="sql plain">, </code><code class="sql string">'sql服务器名 '</code><code class="sql plain">; </code><code class="sql string">'用户名 '</code><code class="sql plain">; </code><code class="sql string">'密码 '</code><code class="sql plain">,数据库名.dbo.表名)</code></div><div class="line number368 index367 alt1"><code class="sql comments">--生成本地表</code></div><div class="line number369 index368 alt2"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">into</code> <code class="sql plain">表 </code><code class="sql keyword">from</code> <code class="sql plain">openrowset( </code><code class="sql string">'SQLOLEDB '</code><code class="sql plain">, </code><code class="sql string">'sql服务器名 '</code><code class="sql plain">; </code><code class="sql string">'用户名 '</code><code class="sql plain">; </code><code class="sql string">'密码 '</code><code class="sql plain">,数据库名.dbo.表名)</code></div><div class="line number370 index369 alt1">&nbsp;</div><div class="line number371 index370 alt2"><code class="sql comments">--把本地表导入远程表</code></div><div class="line number372 index371 alt1"><code class="sql keyword">insert</code> <code class="sql plain">openrowset( </code><code class="sql string">'SQLOLEDB '</code><code class="sql plain">, </code><code class="sql string">'sql服务器名 '</code><code class="sql plain">; </code><code class="sql string">'用户名 '</code><code class="sql plain">; </code><code class="sql string">'密码 '</code><code class="sql plain">,数据库名.dbo.表名)</code></div><div class="line number373 index372 alt2"><code class="sql keyword">select</code> <code class="sql plain">*</code><code class="sql keyword">from</code> <code class="sql plain">本地表</code></div><div class="line number374 index373 alt1"><code class="sql comments">--更新本地表</code></div><div class="line number375 index374 alt2"><code class="sql keyword">update</code> <code class="sql plain">b</code></div><div class="line number376 index375 alt1"><code class="sql keyword">set</code> <code class="sql plain">b.列A=a.列A</code></div><div class="line number377 index376 alt2"><code class="sql spaces">&nbsp;</code><code class="sql keyword">from</code> <code class="sql plain">openrowset( </code><code class="sql string">'SQLOLEDB '</code><code class="sql plain">, </code><code class="sql string">'sql服务器名 '</code><code class="sql plain">; </code><code class="sql string">'用户名 '</code><code class="sql plain">; </code><code class="sql string">'密码 '</code><code class="sql plain">,数据库名.dbo.表名)</code><code class="sql keyword">as</code> <code class="sql plain">a </code><code class="sql keyword">inner</code> <code class="sql color1">join</code> <code class="sql plain">本地表 b</code></div><div class="line number378 index377 alt1"><code class="sql keyword">on</code> <code class="sql plain">a.column1=b.column1</code></div><div class="line number379 index378 alt2"><code class="sql comments">--openquery用法需要创建一个连接</code></div><div class="line number380 index379 alt1"><code class="sql comments">--首先创建一个连接创建链接服务器</code></div><div class="line number381 index380 alt2"><code class="sql keyword">exec</code> <code class="sql plain">sp_addlinkedserver&nbsp;&nbsp; </code><code class="sql string">'ITSV '</code><code class="sql plain">, </code><code class="sql string">' '</code><code class="sql plain">, </code><code class="sql string">'SQLOLEDB '</code><code class="sql plain">, </code><code class="sql string">'远程服务器名或ip地址 '</code></div><div class="line number382 index381 alt1"><code class="sql comments">--查询</code></div><div class="line number383 index382 alt2"><code class="sql keyword">select</code> <code class="sql plain">*</code></div><div class="line number384 index383 alt1"><code class="sql keyword">FROM</code> <code class="sql plain">openquery(ITSV,&nbsp; </code><code class="sql string">'SELECT *&nbsp; FROM 数据库.dbo.表名 '</code><code class="sql plain">)</code></div><div class="line number385 index384 alt2"><code class="sql comments">--把本地表导入远程表</code></div><div class="line number386 index385 alt1"><code class="sql keyword">insert</code> <code class="sql plain">openquery(ITSV,&nbsp; </code><code class="sql string">'SELECT *&nbsp; FROM 数据库.dbo.表名 '</code><code class="sql plain">)</code></div><div class="line number387 index386 alt2"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">本地表</code></div><div class="line number388 index387 alt1"><code class="sql comments">--更新本地表</code></div><div class="line number389 index388 alt2"><code class="sql keyword">update</code> <code class="sql plain">b</code></div><div class="line number390 index389 alt1"><code class="sql keyword">set</code> <code class="sql plain">b.列B=a.列B</code></div><div class="line number391 index390 alt2"><code class="sql keyword">FROM</code> <code class="sql plain">openquery(ITSV,&nbsp; </code><code class="sql string">'SELECT * FROM 数据库.dbo.表名 '</code><code class="sql plain">) </code><code class="sql keyword">as</code> <code class="sql plain">a </code></div><div class="line number392 index391 alt1"><code class="sql keyword">inner</code> <code class="sql color1">join</code> <code class="sql plain">本地表 b </code><code class="sql keyword">on</code> <code class="sql plain">a.列A=b.列A</code></div><div class="line number393 index392 alt2">&nbsp;</div><div class="line number394 index393 alt1"><code class="sql comments">--3、opendatasource/openrowset</code></div><div class="line number395 index394 alt2"><code class="sql keyword">SELECT</code>&nbsp;&nbsp; <code class="sql plain">*</code></div><div class="line number396 index395 alt1"><code class="sql keyword">FROM</code>&nbsp;&nbsp; <code class="sql plain">opendatasource( </code><code class="sql string">'SQLOLEDB '</code><code class="sql plain">,&nbsp; </code><code class="sql string">'Data Source=ip/ServerName;User ID=登陆名;Password=密码 '</code> <code class="sql plain">).test.dbo.roy_ta</code></div><div class="line number397 index396 alt2"><code class="sql comments">--把本地表导入远程表</code></div><div class="line number398 index397 alt1"><code class="sql keyword">insert</code> <code class="sql plain">opendatasource( </code><code class="sql string">'SQLOLEDB '</code><code class="sql plain">,&nbsp; </code><code class="sql string">'Data Source=ip/ServerName;User ID=登陆名;Password=密码 '</code><code class="sql plain">).数据库.dbo.表名</code></div><div class="line number399 index398 alt2"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">本地表 </code></div><div class="line number400 index399 alt1"><code class="sql plain">SQL Server基本函数</code></div><div class="line number401 index400 alt2"><code class="sql plain">SQL Server基本函数</code></div><div class="line number402 index401 alt1"><code class="sql plain">1.字符串函数 长度与分析用</code></div><div class="line number403 index402 alt2"><code class="sql plain">1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格</code></div><div class="line number404 index403 alt1"><code class="sql plain">2,</code><code class="sql color2">substring</code><code class="sql plain">(expression,start,length) 取子串，字符串的下标是从“1”，start为起始位置，length为字符串长度，实际应用中以len(expression)取得其长度</code></div><div class="line number405 index404 alt2"><code class="sql plain">3,</code><code class="sql color2">right</code><code class="sql plain">(char_expr,int_expr) 返回字符串右边第int_expr个字符，还用</code><code class="sql color2">left</code><code class="sql plain">于之相反</code></div><div class="line number406 index405 alt1"><code class="sql plain">4,</code><code class="sql color2">isnull</code><code class="sql plain">( check_expression , replacement_value )如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression字符操作类</code></div><div class="line number407 index406 alt2"><code class="sql plain">5,Sp_addtype 自定義數據類型</code></div><div class="line number408 index407 alt1"><code class="sql plain">例如：</code><code class="sql keyword">EXEC</code> <code class="sql plain">sp_addtype birthday, datetime, </code><code class="sql string">'NULL'</code></div><div class="line number409 index408 alt2"><code class="sql plain">6,</code><code class="sql keyword">set</code> <code class="sql plain">nocount {</code><code class="sql keyword">on</code><code class="sql plain">|</code><code class="sql keyword">off</code><code class="sql plain">}</code></div><div class="line number410 index409 alt1"><code class="sql plain">使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。</code><code class="sql keyword">SET</code> <code class="sql plain">NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。</code></div><div class="line number411 index410 alt2"><code class="sql keyword">SET</code> <code class="sql plain">NOCOUNT 为 </code><code class="sql keyword">ON</code> <code class="sql plain">时，不返回计数（表示受 Transact-SQL 语句影响的行数）。</code></div><div class="line number412 index411 alt1"><code class="sql keyword">SET</code> <code class="sql plain">NOCOUNT 为 </code><code class="sql keyword">OFF</code> <code class="sql plain">时，返回计数</code></div><div class="line number413 index412 alt2"><code class="sql plain">常识</code></div><div class="line number414 index413 alt1">&nbsp;</div><div class="line number415 index414 alt2"><code class="sql plain">在SQL查询中：</code><code class="sql keyword">from</code><code class="sql plain">后最多可以跟多少张表或视图：256</code></div><div class="line number416 index415 alt1"><code class="sql plain">在SQL语句中出现 </code><code class="sql keyword">Order</code> <code class="sql keyword">by</code><code class="sql plain">,查询时，先排序，后取</code></div><div class="line number417 index416 alt2"><code class="sql plain">在SQL中，一个字段的最大容量是8000，而对于nvarchar(4000),由于nvarchar是Unicode码。 </code></div><div class="line number418 index417 alt1"><code class="sql spaces">&nbsp;</code>&nbsp;</div><div class="line number419 index418 alt2"><code class="sql plain">SQLServer2000同步复制技术实现步骤</code></div><div class="line number420 index419 alt1"><code class="sql plain">一、 预备工作</code></div><div class="line number421 index420 alt2"><code class="sql plain">1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户</code></div><div class="line number422 index421 alt1"><code class="sql comments">--管理工具</code></div><div class="line number423 index422 alt2"><code class="sql comments">--计算机管理</code></div><div class="line number424 index423 alt1"><code class="sql comments">--用户和组</code></div><div class="line number425 index424 alt2"><code class="sql comments">--右键用户</code></div><div class="line number426 index425 alt1"><code class="sql comments">--新建用户</code></div><div class="line number427 index426 alt2"><code class="sql comments">--建立一个隶属于administrator组的登陆windows的用户（SynUser）</code></div><div class="line number428 index427 alt1"><code class="sql plain">2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:</code></div><div class="line number429 index428 alt2"><code class="sql plain">我的电脑</code><code class="sql comments">--D:\ 新建一个目录,名为: PUB</code></div><div class="line number430 index429 alt1"><code class="sql comments">--右键这个新建的目录</code></div><div class="line number431 index430 alt2"><code class="sql comments">--属性--共享</code></div><div class="line number432 index431 alt1"><code class="sql comments">--选择"共享该文件夹"</code></div><div class="line number433 index432 alt2"><code class="sql comments">--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限</code></div><div class="line number434 index433 alt1">&nbsp;</div><div class="line number435 index434 alt2"><code class="sql comments">--确定</code></div><div class="line number436 index435 alt1"><code class="sql plain">3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)</code></div><div class="line number437 index436 alt2"><code class="sql plain">开始</code><code class="sql comments">--程序--管理工具--服务</code></div><div class="line number438 index437 alt1"><code class="sql comments">--右键SQLSERVERAGENT</code></div><div class="line number439 index438 alt2"><code class="sql comments">--属性--登陆--选择"此账户"</code></div><div class="line number440 index439 alt1"><code class="sql comments">--输入或者选择第一步中创建的windows登录用户名（SynUser）</code></div><div class="line number441 index440 alt2"><code class="sql comments">--"密码"中输入该用户的密码</code></div><div class="line number442 index441 alt1"><code class="sql plain">4.设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)</code></div><div class="line number443 index442 alt2"><code class="sql plain">企业管理器</code></div><div class="line number444 index443 alt1"><code class="sql comments">--右键SQL实例--属性</code></div><div class="line number445 index444 alt2"><code class="sql comments">--安全性--身份验证</code></div><div class="line number446 index445 alt1"><code class="sql comments">--选择"SQL Server 和 Windows"</code></div><div class="line number447 index446 alt2"><code class="sql comments">--确定</code></div><div class="line number448 index447 alt1"><code class="sql plain">5.在发布服务器和订阅服务器上互相注册</code></div><div class="line number449 index448 alt2"><code class="sql plain">企业管理器</code></div><div class="line number450 index449 alt1"><code class="sql comments">--右键SQL Server组</code></div><div class="line number451 index450 alt2"><code class="sql comments">--新建SQL Server注册...</code></div><div class="line number452 index451 alt1"><code class="sql comments">--下一步--可用的服务器中,输入你要注册的远程服务器名 --添加</code></div><div class="line number453 index452 alt2"><code class="sql comments">--下一步--连接使用,选择第二个"SQL Server身份验证"</code></div><div class="line number454 index453 alt1"><code class="sql comments">--下一步--输入用户名和密码（SynUser）</code></div><div class="line number455 index454 alt2"><code class="sql comments">--下一步--选择SQL Server组,也可以创建一个新组</code></div><div class="line number456 index455 alt1"><code class="sql comments">--下一步--完成</code></div><div class="line number457 index456 alt2"><code class="sql plain">6.对于只能用IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到）</code></div><div class="line number458 index457 alt1"><code class="sql spaces">&nbsp;</code><code class="sql plain">(在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)</code></div><div class="line number459 index458 alt2"><code class="sql plain">开始</code><code class="sql comments">--程序--Microsoft SQL Server--客户端网络实用工具</code></div><div class="line number460 index459 alt1"><code class="sql comments">--别名--添加</code></div><div class="line number461 index460 alt2"><code class="sql comments">--网络库选择"tcp/ip"--服务器别名输入SQL服务器名</code></div><div class="line number462 index461 alt1"><code class="sql comments">--连接参数--服务器名称中输入SQL服务器ip地址</code></div><div class="line number463 index462 alt2"><code class="sql comments">--如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号</code></div><div class="line number464 index463 alt1"><code class="sql plain">二、 正式配置</code></div><div class="line number465 index464 alt2"><code class="sql plain">1、配置发布服务器</code></div><div class="line number466 index465 alt1"><code class="sql plain">打开企业管理器，在发布服务器（B、C、D）上执行以下步骤:</code></div><div class="line number467 index466 alt2"><code class="sql plain">(1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导</code></div><div class="line number468 index467 alt1"><code class="sql plain">(2) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他sql的服务器（选择自己）</code></div><div class="line number469 index468 alt2"><code class="sql plain">(3) [下一步] 设置快照文件夹</code></div><div class="line number470 index469 alt1"><code class="sql plain">采用默认\\servername\Pub</code></div><div class="line number471 index470 alt2"><code class="sql plain">(4) [下一步] 自定义配置</code></div><div class="line number472 index471 alt1"><code class="sql plain">可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置</code></div><div class="line number473 index472 alt2"><code class="sql plain">否,使用下列默认设置（推荐）</code></div><div class="line number474 index473 alt1"><code class="sql plain">(5) [下一步] 设置分发数据库名称和位置 采用默认值</code></div><div class="line number475 index474 alt2"><code class="sql plain">(6) [下一步] 启用发布服务器 选择作为发布的服务器</code></div><div class="line number476 index475 alt1"><code class="sql plain">(7) [下一步] 选择需要发布的数据库和发布类型</code></div><div class="line number477 index476 alt2"><code class="sql plain">(8) [下一步] 选择注册订阅服务器</code></div><div class="line number478 index477 alt1"><code class="sql plain">(9) [下一步] 完成配置</code></div><div class="line number479 index478 alt2"><code class="sql plain">2、创建出版物</code></div><div class="line number480 index479 alt1"><code class="sql plain">发布服务器B、C、D上</code></div><div class="line number481 index480 alt2"><code class="sql plain">(1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令</code></div><div class="line number482 index481 alt1"><code class="sql plain">(2)选择要创建出版物的数据库，然后单击[创建发布]</code></div><div class="line number483 index482 alt2"><code class="sql plain">(3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)</code></div><div class="line number484 index483 alt1"><code class="sql plain">(4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,</code></div><div class="line number485 index484 alt2"><code class="sql plain">SQLSERVER允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。</code></div><div class="line number486 index485 alt1"><code class="sql plain">但是在这里我们选择运行</code><code class="sql string">"SQL SERVER 2000"</code><code class="sql plain">的数据库服务器</code></div><div class="line number487 index486 alt2"><code class="sql plain">(5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表</code></div><div class="line number488 index487 alt1"><code class="sql plain">注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表</code></div><div class="line number489 index488 alt2"><code class="sql plain">(6)选择发布名称和描述</code></div><div class="line number490 index489 alt1"><code class="sql plain">(7)自定义发布属性 向导提供的选择:</code></div><div class="line number491 index490 alt2"><code class="sql plain">是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性</code></div><div class="line number492 index491 alt1"><code class="sql plain">否 根据指定方式创建发布 （建议采用自定义的方式）</code></div><div class="line number493 index492 alt2"><code class="sql plain">(8)[下一步] 选择筛选发布的方式</code></div><div class="line number494 index493 alt1"><code class="sql plain">(9)[下一步] 可以选择是否允许匿名订阅</code></div><div class="line number495 index494 alt2"><code class="sql plain">1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器</code></div><div class="line number496 index495 alt1"><code class="sql plain">方法: [工具]-&gt;[复制]-&gt;[配置发布、订阅服务器和分发的属性]-&gt;[订阅服务器] 中添加</code></div><div class="line number497 index496 alt2"><code class="sql plain">否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅</code></div><div class="line number498 index497 alt1"><code class="sql plain">如果仍然需要匿名订阅则用以下解决办法</code></div><div class="line number499 index498 alt2"><code class="sql plain">[企业管理器]-&gt;[复制]-&gt;[发布内容]-&gt;[属性]-&gt;[订阅选项] 选择允许匿名请求订阅</code></div><div class="line number500 index499 alt1"><code class="sql plain">2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示</code></div><div class="line number501 index500 alt2"><code class="sql plain">(10)[下一步] 设置快照 代理程序调度</code></div><div class="line number502 index501 alt1"><code class="sql plain">(11)[下一步] 完成配置</code></div><div class="line number503 index502 alt2"><code class="sql plain">当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库</code></div><div class="line number504 index503 alt1"><code class="sql plain">有数据</code></div><div class="line number505 index504 alt2"><code class="sql plain">srv1.库名..author有字段:id,</code><code class="sql keyword">name</code><code class="sql plain">,phone,</code></div><div class="line number506 index505 alt1"><code class="sql plain">srv2.库名..author有字段:id,</code><code class="sql keyword">name</code><code class="sql plain">,telphone,adress</code></div><div class="line number507 index506 alt2">&nbsp;</div><div class="line number508 index507 alt1"><code class="sql plain">要求：</code></div><div class="line number509 index508 alt2"><code class="sql plain">srv1.库名..author增加记录则srv1.库名..author记录增加</code></div><div class="line number510 index509 alt1"><code class="sql plain">srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新</code></div><div class="line number511 index510 alt2"><code class="sql comments">--*/</code></div><div class="line number512 index511 alt1">&nbsp;</div><div class="line number513 index512 alt2"><code class="sql comments">--大致的处理步骤</code></div><div class="line number514 index513 alt1"><code class="sql comments">--1.在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步</code></div><div class="line number515 index514 alt2"><code class="sql keyword">exec</code> <code class="sql plain">sp_addlinkedserver </code><code class="sql string">'srv2'</code><code class="sql plain">,</code><code class="sql string">''</code><code class="sql plain">,</code><code class="sql string">'SQLOLEDB'</code><code class="sql plain">,</code><code class="sql string">'srv2的sql实例名或ip'</code></div><div class="line number516 index515 alt1"><code class="sql keyword">exec</code> <code class="sql plain">sp_addlinkedsrvlogin </code><code class="sql string">'srv2'</code><code class="sql plain">,</code><code class="sql string">'false'</code><code class="sql plain">,</code><code class="sql color1">null</code><code class="sql plain">,</code><code class="sql string">'用户名'</code><code class="sql plain">,</code><code class="sql string">'密码'</code></div><div class="line number517 index516 alt2"><code class="sql plain">go</code></div><div class="line number518 index517 alt1"><code class="sql comments">--2.在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动</code></div><div class="line number519 index518 alt2"><code class="sql plain">。我的电脑</code><code class="sql comments">--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动</code></div><div class="line number520 index519 alt1"><code class="sql plain">go</code></div><div class="line number521 index520 alt2">&nbsp;</div><div class="line number522 index521 alt1">&nbsp;</div><div class="line number523 index522 alt2"><code class="sql comments">--然后创建一个作业定时调用上面的同步处理存储过程就行了</code></div><div class="line number524 index523 alt1">&nbsp;</div><div class="line number525 index524 alt2"><code class="sql plain">企业管理器</code></div><div class="line number526 index525 alt1"><code class="sql comments">--管理</code></div><div class="line number527 index526 alt2"><code class="sql comments">--SQL Server代理</code></div><div class="line number528 index527 alt1"><code class="sql comments">--右键作业</code></div><div class="line number529 index528 alt2"><code class="sql comments">--新建作业</code></div><div class="line number530 index529 alt1"><code class="sql comments">--"常规"项中输入作业名称</code></div><div class="line number531 index530 alt2"><code class="sql comments">--"步骤"项</code></div><div class="line number532 index531 alt1"><code class="sql comments">--新建</code></div><div class="line number533 index532 alt2"><code class="sql comments">--"步骤名"中输入步骤名</code></div><div class="line number534 index533 alt1"><code class="sql comments">--"类型"中选择"Transact-SQL 脚本(TSQL)"</code></div><div class="line number535 index534 alt2"><code class="sql comments">--"数据库"选择执行命令的数据库</code></div><div class="line number536 index535 alt1"><code class="sql comments">--"命令"中输入要执行的语句: exec p_process</code></div><div class="line number537 index536 alt2"><code class="sql comments">--确定</code></div><div class="line number538 index537 alt1"><code class="sql comments">--"调度"项</code></div><div class="line number539 index538 alt2"><code class="sql comments">--新建调度</code></div><div class="line number540 index539 alt1"><code class="sql comments">--"名称"中输入调度名称</code></div><div class="line number541 index540 alt2"><code class="sql comments">--"调度类型"中选择你的作业执行安排</code></div><div class="line number542 index541 alt1"><code class="sql comments">--如果选择"反复出现"</code></div><div class="line number543 index542 alt2"><code class="sql comments">--点"更改"来设置你的时间安排</code></div><div class="line number544 index543 alt1">&nbsp;</div><div class="line number545 index544 alt2">&nbsp;</div><div class="line number546 index545 alt1"><code class="sql plain">然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行</code></div><div class="line number547 index546 alt2">&nbsp;</div><div class="line number548 index547 alt1"><code class="sql plain">设置方法:</code></div><div class="line number549 index548 alt2"><code class="sql plain">我的电脑</code><code class="sql comments">--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.</code></div><div class="line number550 index549 alt1">&nbsp;</div><div class="line number551 index550 alt2">&nbsp;</div><div class="line number552 index551 alt1"><code class="sql comments">--3.实现同步处理的方法2,定时同步</code></div><div class="line number553 index552 alt2">&nbsp;</div><div class="line number554 index553 alt1"><code class="sql comments">--在srv1中创建如下的同步处理存储过程</code></div><div class="line number555 index554 alt2"><code class="sql keyword">create</code> <code class="sql plain">proc p_process</code></div><div class="line number556 index555 alt1"><code class="sql keyword">as</code></div><div class="line number557 index556 alt2"><code class="sql comments">--更新修改过的数据</code></div><div class="line number558 index557 alt1"><code class="sql keyword">update</code> <code class="sql plain">b </code><code class="sql keyword">set</code> <code class="sql keyword">name</code><code class="sql plain">=i.</code><code class="sql keyword">name</code><code class="sql plain">,telphone=i.telphone</code></div><div class="line number559 index558 alt2"><code class="sql keyword">from</code> <code class="sql plain">srv2.库名.dbo.author b,author i</code></div><div class="line number560 index559 alt1"><code class="sql keyword">where</code> <code class="sql plain">b.id=i.id </code><code class="sql color1">and</code></div><div class="line number561 index560 alt2"><code class="sql plain">(b.</code><code class="sql keyword">name</code> <code class="sql plain">&lt;&gt; i.</code><code class="sql keyword">name</code> <code class="sql color1">or</code> <code class="sql plain">b.telphone &lt;&gt; i.telphone)</code></div><div class="line number562 index561 alt1">&nbsp;</div><div class="line number563 index562 alt2"><code class="sql comments">--插入新增的数据</code></div><div class="line number564 index563 alt1"><code class="sql keyword">insert</code> <code class="sql plain">srv2.库名.dbo.author(id,</code><code class="sql keyword">name</code><code class="sql plain">,telphone)</code></div><div class="line number565 index564 alt2"><code class="sql keyword">select</code> <code class="sql plain">id,</code><code class="sql keyword">name</code><code class="sql plain">,telphone </code><code class="sql keyword">from</code> <code class="sql plain">author i</code></div><div class="line number566 index565 alt1"><code class="sql keyword">where</code> <code class="sql color1">not</code> <code class="sql plain">exists(</code></div><div class="line number567 index566 alt2"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">srv2.库名.dbo.author </code><code class="sql keyword">where</code> <code class="sql plain">id=i.id)</code></div><div class="line number568 index567 alt1">&nbsp;</div><div class="line number569 index568 alt2"><code class="sql comments">--删除已经删除的数据(如果需要的话)</code></div><div class="line number570 index569 alt1"><code class="sql keyword">delete</code> <code class="sql plain">b</code></div><div class="line number571 index570 alt2"><code class="sql keyword">from</code> <code class="sql plain">srv2.库名.dbo.author b</code></div><div class="line number572 index571 alt1"><code class="sql keyword">where</code> <code class="sql color1">not</code> <code class="sql plain">exists(</code></div><div class="line number573 index572 alt2"><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">author </code><code class="sql keyword">where</code> <code class="sql plain">id=b.id)</code></div><div class="line number574 index573 alt1"><code class="sql plain">go</code></div></div></td></tr></tbody></table></div></div>
</div>
<br></div><div id="MySignature"></div>
<div class="clear"></div>
<div id="blog_post_info_block">
<div id="BlogPostCategory">分类: <a href="http://www.cnblogs.com/yunf/category/291916.html" target="_blank">MySql</a></div>
<div id="EntryTag"></div>
<div id="blog_post_info"><div id="green_channel">
        <a href="javascript:void(0);" id="green_channel_digg" onclick="DiggIt(2013448,cb_blogId,1);green_channel_success(this,&#39;谢谢推荐！&#39;);">好文要顶</a>
            <a id="green_channel_follow" onclick="follow(&#39;aa34e74c-2654-e011-a53f-842b2b196315&#39;);" href="javascript:void(0);">关注我</a>
    <a id="green_channel_favorite" onclick="AddToWz(cb_entryId);return false;" href="javascript:void(0);">收藏该文</a>
    <a id="green_channel_weibo" href="javascript:void(0);" title="分享至新浪微博" onclick="ShareToTsina()"><img src="./mysql sql语句大全 - yfProgramer - 博客园_files/icon_weibo_24.png" alt=""></a>
    <a id="green_channel_wechat" href="javascript:void(0);" title="分享至微信" onclick="shareOnWechat()"><img src="./mysql sql语句大全 - yfProgramer - 博客园_files/wechat.png" alt=""></a>
</div>
<div id="author_profile">
    <div id="author_profile_info" class="author_profile_info">
            <a href="http://home.cnblogs.com/u/yunf/" target="_blank"><img src="./mysql sql语句大全 - yfProgramer - 博客园_files/sample_face.gif" class="author_avatar" alt=""></a>
        <div id="author_profile_detail" class="author_profile_info">
            <a href="http://home.cnblogs.com/u/yunf/">yfProgramer</a><br>
            <a href="http://home.cnblogs.com/u/yunf/followees">关注 - 0</a><br>
            <a href="http://home.cnblogs.com/u/yunf/followers">粉丝 - 127</a>
        </div>
    </div>
    <div class="clear"></div>
    <div id="author_profile_honor"></div>
    <div id="author_profile_follow">
                <a href="javascript:void(0);" onclick="follow(&#39;aa34e74c-2654-e011-a53f-842b2b196315&#39;);return false;">+加关注</a>
    </div>
</div>
<div id="div_digg">
    <div class="diggit" onclick="votePost(2013448,&#39;Digg&#39;)">
        <span class="diggnum" id="digg_count">63</span>
    </div>
    <div class="buryit" onclick="votePost(2013448,&#39;Bury&#39;)">
        <span class="burynum" id="bury_count">4</span>
    </div>
    <div class="clear"></div>
    <div class="diggword" id="digg_tips">
    </div>
</div>
</div>
<div class="clear"></div>
<div id="post_next_prev"><a href="http://www.cnblogs.com/yunf/archive/2011/04/04/2005302.html" class="p_n_p_prefix">« </a> 上一篇：<a href="http://www.cnblogs.com/yunf/archive/2011/04/04/2005302.html" title="发布于2011-04-04 17:48">MySql创建表</a><br><a href="http://www.cnblogs.com/yunf/archive/2011/04/20/2021830.html" class="p_n_p_prefix">» </a> 下一篇：<a href="http://www.cnblogs.com/yunf/archive/2011/04/20/2021830.html" title="发布于2011-04-20 09:01">回车符和换行符区别</a><br></div>
</div>


	<div class="postDesc">posted on <span id="post-date">2011-04-12 11:23</span> <a href="http://www.cnblogs.com/yunf/">yfProgramer</a> 阅读(<span id="post_view_count">399446</span>) 评论(<span id="post_comment_count">31</span>)  <a href="https://i.cnblogs.com/EditPosts.aspx?postid=2013448" rel="nofollow">编辑</a> <a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#" onclick="AddToWz(2013448);return false;">收藏</a></div>
</div>
<script type="text/javascript">var allowComments=true,cb_blogId=85778,cb_entryId=2013448,cb_blogApp=currentBlogApp,cb_blogUserGuid='aa34e74c-2654-e011-a53f-842b2b196315',cb_entryCreatedDate='2011/4/12 11:23:00';loadViewCount(cb_entryId);var cb_postType=1;</script>

</div><a name="!comments"></a><div id="blog-comments-placeholder"><div id="comments_pager_top"></div>
<!--done-->
<br>
<b>FeedBack:</b>
<div class="feedbackNoItems"></div>
	

		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2400698" class="layer">#1楼</a><a name="2400698" id="comment_anchor_2400698"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2012-06-14 11:21</span> | <a id="a_comment_author_2400698" href="http://www.cnblogs.com/ufoet/" target="_blank">Frank LiuWB</a> <a href="http://msg.cnblogs.com/send/Frank%20LiuWB" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2400698" class="blog_comment_body">学习了</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2400698,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2400698,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_2400698_avatar" style="display:none;">http://pic.cnblogs.com/face/u200434.jpg</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2400698,&#39;wBd+hGRF6IbLTFCIdYTeCNasfy7uj4j3m9no0y0w3l76VLcLS7grzQ==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2400698,&#39;wBd+hGRF6IbLTFCIdYTeCNasfy7uj4j3m9no0y0w3l76VLcLS7grzQ==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2601020" class="layer">#2楼</a><a name="2601020" id="comment_anchor_2601020"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2013-01-16 09:58</span> | <a id="a_comment_author_2601020" href="http://www.cnblogs.com/wangfengcool/" target="_blank">code_boy_ing</a> <a href="http://msg.cnblogs.com/send/code_boy_ing" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2601020" class="blog_comment_body">学习了</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2601020,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2601020,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_2601020_avatar" style="display:none;">http://pic.cnblogs.com/face/u489705.jpg?id=15181100</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2601020,&#39;rxdOqCDyn0lVrOV5bQuIhzFVAdbCTVIIbDXD7i3jiOsjpiNxO1UjyQ==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2601020,&#39;rxdOqCDyn0lVrOV5bQuIhzFVAdbCTVIIbDXD7i3jiOsjpiNxO1UjyQ==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2609577" class="layer">#3楼</a><a name="2609577" id="comment_anchor_2609577"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2013-01-28 23:57</span> | <a id="a_comment_author_2609577" href="http://www.cnblogs.com/aa-java/" target="_blank">*小叼……朱</a> <a href="http://msg.cnblogs.com/send/%2A%E5%B0%8F%E5%8F%BC%E2%80%A6%E2%80%A6%E6%9C%B1" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2609577" class="blog_comment_body">很完整</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2609577,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2609577,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_2609577_avatar" style="display:none;">http://pic.cnblogs.com/face/u400090.jpg?id=18151405</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2609577,&#39;wqrpxdUVQE6VwxEppm3O/E+6jmFvSlDfJOQxtK7OOPWS+AAvGZhFBA==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2609577,&#39;wqrpxdUVQE6VwxEppm3O/E+6jmFvSlDfJOQxtK7OOPWS+AAvGZhFBA==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2739043" class="layer">#4楼</a><a name="2739043" id="comment_anchor_2739043"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2013-07-30 15:27</span> | <a id="a_comment_author_2739043" href="http://www.cnblogs.com/simpman/" target="_blank">simpman</a> <a href="http://msg.cnblogs.com/send/simpman" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2739043" class="blog_comment_body">全，学习了。</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2739043,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2739043,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_2739043_avatar" style="display:none;">http://pic.cnblogs.com/face/467192/20130524091552.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2739043,&#39;WQ2ptb0eQWSkyfIcHu+9/rpq7xs7aTfLN5HCgA4E+2HDQLH/zsbtXQ==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2739043,&#39;WQ2ptb0eQWSkyfIcHu+9/rpq7xs7aTfLN5HCgA4E+2HDQLH/zsbtXQ==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2762589" class="layer">#5楼</a><a name="2762589" id="comment_anchor_2762589"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2013-08-28 15:00</span> | <a id="a_comment_author_2762589" href="http://home.cnblogs.com/u/561417/" target="_blank">情天恨海</a> <a href="http://msg.cnblogs.com/send/%E6%83%85%E5%A4%A9%E6%81%A8%E6%B5%B7" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2762589" class="blog_comment_body">顶，很好，很强大！整好在做统计报表，排上了用场。</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2762589,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2762589,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2762589,&#39;NtSMfiO0+UQPjyPIFIxMfXdZXepRzoIF5xNAr8vDeGYWAmhwjX2VAw==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2762589,&#39;NtSMfiO0+UQPjyPIFIxMfXdZXepRzoIF5xNAr8vDeGYWAmhwjX2VAw==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2766586" class="layer">#6楼</a><a name="2766586" id="comment_anchor_2766586"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2013-09-02 15:44</span> | <a id="a_comment_author_2766586" href="http://www.cnblogs.com/canbefree/" target="_blank">UCanBeFree</a> <a href="http://msg.cnblogs.com/send/UCanBeFree" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2766586" class="blog_comment_body">这个是mysql 还是sql server啊。</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2766586,&#39;Digg&#39;,this)">支持(1)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2766586,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_2766586_avatar" style="display:none;">http://pic.cnblogs.com/face/528589/20130617212405.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2766586,&#39;H/wrdfAYqzB5nzTa24UxX7wJ7jdzdTSAOOo6J2rwiW5zGmAxzqNnIQ==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2766586,&#39;H/wrdfAYqzB5nzTa24UxX7wJ7jdzdTSAOOo6J2rwiW5zGmAxzqNnIQ==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2768992" class="layer">#7楼</a><a name="2768992" id="comment_anchor_2768992"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2013-09-05 11:32</span> | <a id="a_comment_author_2768992" href="http://home.cnblogs.com/u/564792/" target="_blank">liusc</a> <a href="http://msg.cnblogs.com/send/liusc" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2768992" class="blog_comment_body">@<br>create table tabLedInfo as select id,name from ledinfo where 1=2<br>或者：<br>create table tabLedInfo as select id,name from ledinfo  <br><br>definition  only 只是一个定义条件，定义新表是否有原表的数据。不知道这个理解对不对。</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2768992,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2768992,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2768992,&#39;a2vlnNOM4VWSuNiLNXTwVk5Gnmgg0u+fHYxQD7KJiTG+BBAfCvVJRA==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2768992,&#39;a2vlnNOM4VWSuNiLNXTwVk5Gnmgg0u+fHYxQD7KJiTG+BBAfCvVJRA==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2769221" class="layer">#8楼</a><a name="2769221" id="comment_anchor_2769221"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2013-09-05 15:49</span> | <a id="a_comment_author_2769221" href="http://home.cnblogs.com/u/564792/" target="_blank">liusc</a> <a href="http://msg.cnblogs.com/send/liusc" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2769221" class="blog_comment_body">最大：select max(field1) as maxvalue from table1 <br>maxvalue是系统函数，不能取这个别名。</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2769221,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2769221,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2769221,&#39;a2vlnNOM4VWSuNiLNXTwVk5Gnmgg0u+fHYxQD7KJiTG+BBAfCvVJRA==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2769221,&#39;a2vlnNOM4VWSuNiLNXTwVk5Gnmgg0u+fHYxQD7KJiTG+BBAfCvVJRA==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2836448" class="layer">#9楼</a><a name="2836448" id="comment_anchor_2836448"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2013-12-13 10:34</span> | <a id="a_comment_author_2836448" href="http://www.cnblogs.com/fornever/" target="_blank">_Never_</a> <a href="http://msg.cnblogs.com/send/_Never_" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2836448" class="blog_comment_body">真好，收藏了算了</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2836448,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2836448,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2836448,&#39;NS1GzND3dJv2cbIZwGGfOYoWQ5FvbD0F+/AVX+qdKZ/KtTRQvbemIg==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2836448,&#39;NS1GzND3dJv2cbIZwGGfOYoWQ5FvbD0F+/AVX+qdKZ/KtTRQvbemIg==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2871889" class="layer">#10楼</a><a name="2871889" id="comment_anchor_2871889"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2014-02-09 13:30</span> | <a id="a_comment_author_2871889" href="http://www.cnblogs.com/xiashuo-he/" target="_blank">FookLook</a> <a href="http://msg.cnblogs.com/send/FookLook" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2871889" class="blog_comment_body">不错的总结</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2871889,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2871889,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_2871889_avatar" style="display:none;">http://pic.cnblogs.com/face/479059/20140902143021.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2871889,&#39;VP6z6VmBOwMg1C4xwSrDl+6U1cuROS8CTnww+Y5T6RB76RA5hoFX6Q==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2871889,&#39;VP6z6VmBOwMg1C4xwSrDl+6U1cuROS8CTnww+Y5T6RB76RA5hoFX6Q==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2968957" class="layer">#11楼</a><a name="2968957" id="comment_anchor_2968957"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2014-06-21 10:16</span> | <a id="a_comment_author_2968957" href="http://www.cnblogs.com/annabook/" target="_blank">山水中华</a> <a href="http://msg.cnblogs.com/send/%E5%B1%B1%E6%B0%B4%E4%B8%AD%E5%8D%8E" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2968957" class="blog_comment_body">能把两种数据库分开说吗</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2968957,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2968957,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2968957,&#39;R3biwDARbyhsnILPenpCQiMIyXdjwp1gLFiwRp0aUW6GgEMuyTzqPQ==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2968957,&#39;R3biwDARbyhsnILPenpCQiMIyXdjwp1gLFiwRp0aUW6GgEMuyTzqPQ==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2974703" class="layer">#12楼</a><a name="2974703" id="comment_anchor_2974703"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2014-07-01 08:58</span> | <a id="a_comment_author_2974703" href="http://home.cnblogs.com/u/646868/" target="_blank">执念、</a> <a href="http://msg.cnblogs.com/send/%E6%89%A7%E5%BF%B5%E3%80%81" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2974703" class="blog_comment_body">String sql="select * from student where id=? like ’%value1%’ ";//3.准备SQL语句<br>这个是我正在练习的一个语句 但是错了  有没有大哥教一下小弟</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2974703,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2974703,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2974703,&#39;eJV2ZHv6WVC08Tml6WDT7akhGTIIhC/7Wk3XjFvcfRJbeawbNDohMA==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2974703,&#39;eJV2ZHv6WVC08Tml6WDT7akhGTIIhC/7Wk3XjFvcfRJbeawbNDohMA==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2974705" class="layer">#13楼</a><a name="2974705" id="comment_anchor_2974705"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2014-07-01 08:58</span> | <a id="a_comment_author_2974705" href="http://home.cnblogs.com/u/646868/" target="_blank">执念、</a> <a href="http://msg.cnblogs.com/send/%E6%89%A7%E5%BF%B5%E3%80%81" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2974705" class="blog_comment_body">Parameter index out of range (2 &gt; number of parameters, which is 1).<br>这个是错误</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2974705,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2974705,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2974705,&#39;eJV2ZHv6WVC08Tml6WDT7akhGTIIhC/7Wk3XjFvcfRJbeawbNDohMA==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2974705,&#39;eJV2ZHv6WVC08Tml6WDT7akhGTIIhC/7Wk3XjFvcfRJbeawbNDohMA==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2974706" class="layer">#14楼</a><a name="2974706" id="comment_anchor_2974706"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2014-07-01 08:59</span> | <a id="a_comment_author_2974706" href="http://home.cnblogs.com/u/646868/" target="_blank">执念、</a> <a href="http://msg.cnblogs.com/send/%E6%89%A7%E5%BF%B5%E3%80%81" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_2974706" class="blog_comment_body">@<br>Parameter index out of range (2 &gt; number of parameters, which is 1).错误是这个样子的</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(2974706,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(2974706,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(2974706,&#39;eJV2ZHv6WVC08Tml6WDT7akhGTIIhC/7Wk3XjFvcfRJbeawbNDohMA==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(2974706,&#39;eJV2ZHv6WVC08Tml6WDT7akhGTIIhC/7Wk3XjFvcfRJbeawbNDohMA==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3038546" class="layer">#15楼</a><a name="3038546" id="comment_anchor_3038546"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2014-10-03 15:34</span> | <a id="a_comment_author_3038546" href="http://home.cnblogs.com/u/642828/" target="_blank">problemKiller</a> <a href="http://msg.cnblogs.com/send/problemKiller" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3038546" class="blog_comment_body">垃圾</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3038546,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3038546,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3038546,&#39;YyzNCXGVUcjQ28iBPKg19vrHnppqxC3jmBP7TmsTXQQN5xFm/j7tgQ==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3038546,&#39;YyzNCXGVUcjQ28iBPKg19vrHnppqxC3jmBP7TmsTXQQN5xFm/j7tgQ==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3135279" class="layer">#16楼</a><a name="3135279" id="comment_anchor_3135279"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2015-03-04 18:10</span> | <a id="a_comment_author_3135279" href="http://www.cnblogs.com/diligenceday/" target="_blank">方方和圆圆</a> <a href="http://msg.cnblogs.com/send/%E6%96%B9%E6%96%B9%E5%92%8C%E5%9C%86%E5%9C%86" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3135279" class="blog_comment_body">louzhu ....</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3135279,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3135279,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_3135279_avatar" style="display:none;">http://pic.cnblogs.com/face/497865/20150117172039.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3135279,&#39;RVtMFEGySi5sEGpIjjIqOnKZeAEmZYoMGi9P7gYcalakrIHsROYYUQ==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3135279,&#39;RVtMFEGySi5sEGpIjjIqOnKZeAEmZYoMGi9P7gYcalakrIHsROYYUQ==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3204905" class="layer">#17楼</a><a name="3204905" id="comment_anchor_3204905"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2015-06-09 23:23</span> | <a id="a_comment_author_3204905" href="http://www.cnblogs.com/qtccf/" target="_blank">秋天吹春风</a> <a href="http://msg.cnblogs.com/send/%E7%A7%8B%E5%A4%A9%E5%90%B9%E6%98%A5%E9%A3%8E" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3204905" class="blog_comment_body">mysql没有select top n的语法吧</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3204905,&#39;Digg&#39;,this)">支持(2)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3204905,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3204905,&#39;4TwHo/NnYfv/FE1ZgVdhqIeq6MpLBKVjhpRU02KHQg5HshPH5z949g==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3204905,&#39;4TwHo/NnYfv/FE1ZgVdhqIeq6MpLBKVjhpRU02KHQg5HshPH5z949g==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3262090" class="layer">#18楼</a><a name="3262090" id="comment_anchor_3262090"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2015-09-07 23:22</span> | <a id="a_comment_author_3262090" href="http://www.cnblogs.com/yangh965/" target="_blank">heyLuer</a> <a href="http://msg.cnblogs.com/send/heyLuer" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3262090" class="blog_comment_body">总结的很全面</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3262090,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3262090,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_3262090_avatar" style="display:none;">http://pic.cnblogs.com/face/805849/20150901170501.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3262090,&#39;fA9fi/D1IOPUpto2IsgLoHqPp9gKxo4TBkZIqxDptqsK8C2VLI6DEA==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3262090,&#39;fA9fi/D1IOPUpto2IsgLoHqPp9gKxo4TBkZIqxDptqsK8C2VLI6DEA==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3336960" class="layer">#19楼</a><a name="3336960" id="comment_anchor_3336960"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2015-12-29 11:25</span> | <a id="a_comment_author_3336960" href="http://www.cnblogs.com/suriski/" target="_blank">凌寒傲雪</a> <a href="http://msg.cnblogs.com/send/%E5%87%8C%E5%AF%92%E5%82%B2%E9%9B%AA" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3336960" class="blog_comment_body">谢谢，我收藏了</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3336960,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3336960,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_3336960_avatar" style="display:none;">http://pic.cnblogs.com/face/813703/20150920021116.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3336960,&#39;Va1n8nPdimpLzDCuWW4vDzkM7iM0nOA5l97aWnHn2sCQzTokii/FIw==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3336960,&#39;Va1n8nPdimpLzDCuWW4vDzkM7iM0nOA5l97aWnHn2sCQzTokii/FIw==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3366649" class="layer">#20楼</a><a name="3366649" id="comment_anchor_3366649"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2016-02-25 13:36</span> | <a id="a_comment_author_3366649" href="http://home.cnblogs.com/u/891148/" target="_blank">nimendoushisb</a> <a href="http://msg.cnblogs.com/send/nimendoushisb" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3366649" class="blog_comment_body"><b>你的题目是 mysql 结果内容都是  sql server<br>你这不是误导吗，我去你妈的吧</b></div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3366649,&#39;Digg&#39;,this)">支持(6)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3366649,&#39;Bury&#39;,this)">反对(2)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3366649,&#39;D6S88YWAgOUYPpaz55bKtm65fdlh6dn+wVap+Tggn1SkTPlJMjRLZg==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3366649,&#39;D6S88YWAgOUYPpaz55bKtm65fdlh6dn+wVap+Tggn1SkTPlJMjRLZg==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3478486" class="layer">#21楼</a><a name="3478486" id="comment_anchor_3478486"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2016-07-28 17:15</span> | <a id="a_comment_author_3478486" href="http://www.cnblogs.com/AkazaAkari/" target="_blank">阿卡林</a> <a href="http://msg.cnblogs.com/send/%E9%98%BF%E5%8D%A1%E6%9E%97" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3478486" class="blog_comment_body">丢你20楼</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3478486,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3478486,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_3478486_avatar" style="display:none;">http://pic.cnblogs.com/face/987953/20161025084326.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3478486,&#39;RVt8Dk2fF1hXWPP2tK6cpqM9XR8x7Sg/6WYkesF9TM668X8A84ZzJg==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3478486,&#39;RVt8Dk2fF1hXWPP2tK6cpqM9XR8x7Sg/6WYkesF9TM668X8A84ZzJg==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3482387" class="layer">#22楼</a><a name="3482387" id="comment_anchor_3482387"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2016-08-03 17:29</span> | <a id="a_comment_author_3482387" href="http://home.cnblogs.com/u/964708/" target="_blank">Lycorisradiata</a> <a href="http://msg.cnblogs.com/send/Lycorisradiata" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3482387" class="blog_comment_body">晕死，不是mysql吗 怎么还有sql server的知识点。</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3482387,&#39;Digg&#39;,this)">支持(1)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3482387,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3482387,&#39;X8ctKG8mTSDoq8jvGZwT3VCl6YPrVSAXevHrLBQbr9/2PiOSfRV1DQ==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3482387,&#39;X8ctKG8mTSDoq8jvGZwT3VCl6YPrVSAXevHrLBQbr9/2PiOSfRV1DQ==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3538904" class="layer">#23楼</a><a name="3538904" id="comment_anchor_3538904"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2016-10-23 16:35</span> | <a id="a_comment_author_3538904" href="http://www.cnblogs.com/yangshichao/" target="_blank">超369</a> <a href="http://msg.cnblogs.com/send/%E8%B6%85369" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3538904" class="blog_comment_body">Mysql 支持 EXCEPT 运算符吗？？？？？？？？？？</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3538904,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3538904,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_3538904_avatar" style="display:none;">http://pic.cnblogs.com/face/608327/20140310210756.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3538904,&#39;vB4oP1ZTE3lmbGQe/eASCULQt82dWdCpOE4xZuYVru7IOzTm+rtpjg==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3538904,&#39;vB4oP1ZTE3lmbGQe/eASCULQt82dWdCpOE4xZuYVru7IOzTm+rtpjg==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3538943" class="layer">#24楼</a><a name="3538943" id="comment_anchor_3538943"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2016-10-23 17:06</span> | <a id="a_comment_author_3538943" href="http://www.cnblogs.com/yangshichao/" target="_blank">超369</a> <a href="http://msg.cnblogs.com/send/%E8%B6%85369" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3538943" class="blog_comment_body">楼主的标题是不是写错了？？看博文内容是Sql Server的知识</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3538943,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3538943,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_3538943_avatar" style="display:none;">http://pic.cnblogs.com/face/608327/20140310210756.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3538943,&#39;vB4oP1ZTE3lmbGQe/eASCULQt82dWdCpOE4xZuYVru7IOzTm+rtpjg==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3538943,&#39;vB4oP1ZTE3lmbGQe/eASCULQt82dWdCpOE4xZuYVru7IOzTm+rtpjg==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3567157" class="layer">#25楼</a><a name="3567157" id="comment_anchor_3567157"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2016-11-28 16:03</span> | <a id="a_comment_author_3567157" href="http://www.cnblogs.com/WhiteM/" target="_blank">rookieM</a> <a href="http://msg.cnblogs.com/send/rookieM" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3567157" class="blog_comment_body">内容满多的，慢慢看</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3567157,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3567157,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_3567157_avatar" style="display:none;">http://pic.cnblogs.com/face/1037764/20161011221819.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3567157,&#39;S/l1bhtAXnhIgcmrBsOseuhYCnHKfOsBRUr2t6lDWPvioEJpF8RBoA==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3567157,&#39;S/l1bhtAXnhIgcmrBsOseuhYCnHKfOsBRUr2t6lDWPvioEJpF8RBoA==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3604340" class="layer">#26楼</a><a name="3604340" id="comment_anchor_3604340"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2017-01-16 13:53</span> | <a id="a_comment_author_3604340" href="http://home.cnblogs.com/u/1052981/" target="_blank">亦枫亦也</a> <a href="http://msg.cnblogs.com/send/%E4%BA%A6%E6%9E%AB%E4%BA%A6%E4%B9%9F" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3604340" class="blog_comment_body">赞</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3604340,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3604340,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3604340,&#39;TWKw1yek5DP5usEFtHKejpKp6qRkWY9O2Tr9jJsAsyfOeB4Xa/s5cA==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3604340,&#39;TWKw1yek5DP5usEFtHKejpKp6qRkWY9O2Tr9jJsAsyfOeB4Xa/s5cA==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3639085" class="layer">#27楼</a><a name="3639085" id="comment_anchor_3639085"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2017-03-13 10:37</span> | <a id="a_comment_author_3639085" href="http://home.cnblogs.com/u/870400/" target="_blank">耍耍小性子</a> <a href="http://msg.cnblogs.com/send/%E8%80%8D%E8%80%8D%E5%B0%8F%E6%80%A7%E5%AD%90" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3639085" class="blog_comment_body">首先在这里感谢下楼主，其次我想对那些素质较低的人说别人辛辛苦苦写出的内容你不爱看可以圆润的走，别在这里强调你的存在感。</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3639085,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3639085,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3639085,&#39;Rpg6eMehMUAstccyGUHY52/lHZBi12TpBCzptFlNXC6WBma1GiMYCA==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3639085,&#39;Rpg6eMehMUAstccyGUHY52/lHZBi12TpBCzptFlNXC6WBma1GiMYCA==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3642534" class="layer">#28楼</a><a name="3642534" id="comment_anchor_3642534"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2017-03-16 15:31</span> | <a id="a_comment_author_3642534" href="http://www.cnblogs.com/yituzimin/" target="_blank">亦兔子敏</a> <a href="http://msg.cnblogs.com/send/%E4%BA%A6%E5%85%94%E5%AD%90%E6%95%8F" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3642534" class="blog_comment_body"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#2769221" title="查看所回复的评论" onclick="commentManager.renderComments(0,50,2769221);">@</a>
liusc<br>看的真仔细 赞</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3642534,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3642534,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_3642534_avatar" style="display:none;">http://pic.cnblogs.com/face/908842/20160727173401.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3642534,&#39;1pvzpT5Tf535x9lXGLOnfRzwd+B+n3HVyVTwTS0H/vXD0LUSWeUHHQ==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3642534,&#39;1pvzpT5Tf535x9lXGLOnfRzwd+B+n3HVyVTwTS0H/vXD0LUSWeUHHQ==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3653112" class="layer">#29楼</a><a name="3653112" id="comment_anchor_3653112"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2017-03-28 08:52</span> | <a id="a_comment_author_3653112" href="http://home.cnblogs.com/u/1135316/" target="_blank">竹染森光</a> <a href="http://msg.cnblogs.com/send/%E7%AB%B9%E6%9F%93%E6%A3%AE%E5%85%89" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3653112" class="blog_comment_body">请注明转载 谢谢</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3653112,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3653112,&#39;Bury&#39;,this)">反对(0)</a></div>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3653112,&#39;Ip8ADCRX6vKwo4YI+pFN0YrRmjv+YZzgC9az4aYo0nSgF3YgzFDPrw==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3653112,&#39;Ip8ADCRX6vKwo4YI+pFN0YrRmjv+YZzgC9az4aYo0nSgF3YgzFDPrw==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3698949" class="layer">#30楼</a><a name="3698949" id="comment_anchor_3698949"></a></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2017-05-23 15:52</span> | <a id="a_comment_author_3698949" href="http://www.cnblogs.com/anuo/" target="_blank">稻草客666</a> <a href="http://msg.cnblogs.com/send/%E7%A8%BB%E8%8D%89%E5%AE%A2666" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3698949" class="blog_comment_body">卧槽 ,这是 sql server 的</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3698949,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3698949,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_3698949_avatar" style="display:none;">http://pic.cnblogs.com/face/479690/20130609084053.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3698949,&#39;MFT8vTu141kip6xrudBsS4kDlkideCRtyHUeLyJC3CSi37I+VLP8XQ==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3698949,&#39;MFT8vTu141kip6xrudBsS4kDlkideCRtyHUeLyJC3CSi37I+VLP8XQ==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	
		<div class="feedbackItem">
			<div class="feedbackListTitle"><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#3754025" class="layer">#31楼</a><a name="3754025" id="comment_anchor_3754025"></a><span id="comment-maxId" style="display:none;">3754025</span><span id="comment-maxDate" style="display:none;">2017/8/9 17:32:31</span></div>
			<div class="feedbackListSubtitle">
				 <span class="comment_date">2017-08-09 17:32</span> | <a id="a_comment_author_3754025" href="http://www.cnblogs.com/hbb0b0/" target="_blank">b0b0</a> <a href="http://msg.cnblogs.com/send/b0b0" title="发送站内短消息" class="sendMsg2This">&nbsp;</a><br>
				<div align="left"><div id="comment_body_3754025" class="blog_comment_body">这是sql server的语句,不是mysql</div><div class="comment_vote"><a href="javascript:void(0);" class="comment_digg" onclick="return voteComment(3754025,&#39;Digg&#39;,this)">支持(0)</a><a href="javascript:void(0);" class="comment_bury" onclick="return voteComment(3754025,&#39;Bury&#39;,this)">反对(0)</a></div><span id="comment_3754025_avatar" style="display:none;">http://pic.cnblogs.com/face/u47642.png</span>&nbsp;&nbsp;<span class="comment_actions"><a href="javascript:void(0);" onclick="return ReplyComment(3754025,&#39;hVBWHcY4fIQcZ3YvOo70kg9w1GNp9C+jbil2fGIKKL/+1aIrySyK2w==&#39;)">回复</a><a href="javascript:void(0);" onclick="return QuoteComment(3754025,&#39;hVBWHcY4fIQcZ3YvOo70kg9w1GNp9C+jbil2fGIKKL/+1aIrySyK2w==&#39;)">引用</a></span></div>
			</div>
			
			
		</div>
	



<div id="comments_pager_bottom"></div></div><script type="text/javascript">var commentManager = new blogCommentManager();commentManager.renderComments(0);</script>
<div id="comment_form" class="commentform">
<a name="commentform"></a>
<div id="divCommentShow"></div>
<div id="comment_nav"><span id="span_refresh_tips"></span><a href="javascript:void(0);" onclick="return RefreshCommentList();" id="lnk_RefreshComments" runat="server" clientidmode="Static">刷新评论</a><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#" onclick="return RefreshPage();">刷新页面</a><a href="https://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html#top">返回顶部</a></div>
<div id="comment_form_container">
<div id="commentform_title">发表评论</div>
<span id="tip_comment" style="color:Red"></span>
<p>
昵称：<input type="text" id="tbCommentAuthor" class="author" disabled="disabled" size="50" value="雷炎">
</p>
<div class="commentbox_main">
<div class="commentbox_title">
<div class="commentbox_title_left">评论内容：</div>
<div class="commentbox_title_right">
<img id="ubb_quote" class="comment_icon" src="./mysql sql语句大全 - yfProgramer - 博客园_files/quote.gif" alt="引用" title="添加引用" onclick="insertUBB(&#39;tbCommentBody&#39;,&#39;quote&#39;)">
<img id="ubb_bold" class="comment_icon" src="./mysql sql语句大全 - yfProgramer - 博客园_files/b.png" alt="粗体" title="添加粗体" onclick="insertUBB(&#39;tbCommentBody&#39;,&#39;b&#39;)">
<img id="ubb_url" class="comment_icon" src="./mysql sql语句大全 - yfProgramer - 博客园_files/lk.png" alt="链接" title="添加链接" onclick="insertUbbUrl(&#39;tbCommentBody&#39;)">
<img id="ubb_indent" class="comment_icon" src="./mysql sql语句大全 - yfProgramer - 博客园_files/indent.png" alt="缩进" title="添加首行缩进" onclick="insertIndent(&#39;tbCommentBody&#39;)">
<img id="ubb_code" class="comment_icon" src="./mysql sql语句大全 - yfProgramer - 博客园_files/InsertCode.gif" alt="代码" title="添加代码" onclick="insertUbbCode()">
<img id="ubb_img" class="comment_icon" src="./mysql sql语句大全 - yfProgramer - 博客园_files/img.gif" alt="图片" title="上传图片" onclick="OpenImageUploadWindow();">
</div>
</div>
<div class="clear"></div>
<textarea id="tbCommentBody" class="comment_textarea"></textarea>
</div>
<p id="commentbox_opt">
<input id="btn_comment_submit" type="button" class="comment_btn" value="提交评论">
<span id="span_comment_canceledit" style="display:none"><a href="javascript:void(0);" onclick="return CancelCommentEdit()">不改了</a></span>
<a href="javascript:void(0);" onclick="return logout();">退出</a>
        <a id="commentbox_opt_sub" href="javascript:void(0);" title="订阅后有新评论时会邮件通知您" onclick="commentManager.Subscribe()">订阅评论</a>
</p>
<div id="tip_comment2" style="color:Red"></div>
<p>
[Ctrl+Enter快捷键提交]
</p>
<div style="display:none">
<span id="comment_edit_id"></span><span id="span_parentcomment_id"></span>
<span id="span_parent_id"></span>
<span id="span_comment_replyto"></span>
<span id="span_comment_posted"></span>
</div>
</div>
<div class="ad_text_commentbox" id="ad_text_under_commentbox"></div>
<div id="ad_t2"></div>
<div id="opt_under_post"></div>
<div id="cnblogs_c1" class="c_ad_block"></div>
<div id="under_post_news"><div class="itnews c_ad_block"><b>最新IT新闻</b>:<br> ·  <a href="http://news.cnblogs.com/n/582890/" target="_blank">微软不是修改源代码来修复bug的？</a><br> ·  <a href="http://news.cnblogs.com/n/582888/" target="_blank">游戏开发者指责微软毁掉Windows Phone之梦</a><br> ·  <a href="http://news.cnblogs.com/n/582889/" target="_blank">全球最大飞艇Airlander 10受损 两名工作人员受伤</a><br> ·  <a href="http://news.cnblogs.com/n/582887/" target="_blank">投行正在让程序员变成交易员接管交易</a><br> ·  <a href="http://news.cnblogs.com/n/582883/" target="_blank">腾讯信用分终于开始启用了：免押金骑摩拜</a><br>» <a href="http://news.cnblogs.com/" title="IT新闻" target="_blank">更多新闻...</a></div></div>
<div id="cnblogs_c2" class="c_ad_block"></div>
<div id="under_post_kb"><div class="itnews c_ad_block" id="kb_block"><b>最新知识库文章</b>:<br><div id="kb_recent"> ·  <a href="http://kb.cnblogs.com/page/112506/" target="_blank">大道至简，职场上做人做事做管理</a><br> ·  <a href="http://kb.cnblogs.com/page/563790/" target="_blank">关于编程，你的练习是不是有效的？</a><br> ·  <a href="http://kb.cnblogs.com/page/581467/" target="_blank">改善程序员生活质量的 3+10 习惯</a><br> ·  <a href="http://kb.cnblogs.com/page/568841/" target="_blank">NASA的10条代码编写原则</a><br> ·  <a href="http://kb.cnblogs.com/page/578502/" target="_blank">为什么你参加了那么多培训，却依然表现平平？</a><br></div>» <a href="http://kb.cnblogs.com/" target="_blank">更多知识库文章...</a></div></div>
<div id="HistoryToday" class="c_ad_block"></div>
<script type="text/javascript">
    fixPostBody();
    setTimeout(function () { incrementViewCount(cb_entryId); }, 50);
    deliverAdT2();
    deliverAdC1();
    deliverAdC2();    
    loadNewsAndKb();
    loadBlogSignature();
    LoadPostInfoBlock(cb_blogId, cb_entryId, cb_blogApp, cb_blogUserGuid);
    GetPrevNextPost(cb_entryId, cb_blogId, cb_entryCreatedDate, cb_postType);
    loadOptUnderPost();
    GetHistoryToday(cb_blogId, cb_blogApp, cb_entryCreatedDate);   
</script>
</div>


</div>

<!--done-->
<div class="footer">
	
	Copyright ©2017 yfProgramer Powered By<a href="https://www.cnblogs.com/">博客园</a> 模板提供：<a href="http://blog.hjenglish.com/">沪江博客</a>
</div>


</body></html>